Fulfills only the last case, and the first 2 are not. Already broke my head. Help me please.

DROP TRIGGER IF EXISTS queue_log; DELIMITER $$ CREATE TRIGGER `queue_log` AFTER insert ON `queue_log` FOR EACH ROW BEGIN CASE WHEN queue_log.`event` = 'COMPLETEAGENT' THEN UPDATE calls SET calls.laststep = 'answered' WHERE calls.uniqueid = NEW.callid; END CASE; CASE WHEN queue_log.`event` = 'COMPLETECALLER' THEN UPDATE calls SET calls.laststep = 'answered' WHERE calls.uniqueid = NEW.callid; END CASE; CASE WHEN queue_log.`event` = 'RINGNOANSWER' THEN DELETE FROM queue_log WHERE queue_log.callid = NEW.callid; END CASE; END$$ DELIMITER ;
  • Not exactly working out? Try to display an error message for each case (in the spirit of SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'case COMPLETEAGENT', MYSQL_ERRNO = 1000; ) and pull the table on which the trigger hangs from the console. - Nofate ♦
  • It would not hurt to attach the queue_log table queue_log . - Nofate ♦

1 answer 1

First of all, it should be noted that the trigger is generally written incorrectly. The CASE/WHEN construct is used differently. Most likely the following was meant:

 DROP TRIGGER IF EXISTS queue_log; DELIMITER $$ CREATE TRIGGER `queue_log` AFTER insert ON `queue_log` FOR EACH ROW BEGIN CASE NEW.`event` # не `queue_log`, а именно NEW WHEN 'COMPLETEAGENT' THEN UPDATE calls SET calls.laststep = 'answered' WHERE calls.uniqueid = NEW.callid; WHEN 'COMPLETECALLER' THEN UPDATE calls SET calls.laststep = 'answered' WHERE calls.uniqueid = NEW.callid; WHEN 'RINGNOANSWER' THEN DELETE FROM queue_log WHERE queue_log.callid = NEW.callid; END CASE; END$$ DELIMITER ;

But even with the correct CASE/WHEN trigger will not work, because in the third branch there is a deletion from the same table, the operation on which triggers the trigger.

The most surprising thing is that it is stated in the question that just the third branch is working, despite both problems.

  • So when adding a line with RINGNOANSWER, he deletes it himself, since This is not a necessary entry. And the lines COMPLETEAGENT and COMPLETECALLER are normally added. He does not erase them, because they do not meet the condition of RINGNOANSWER. Thanks now I'll try - Andrey
  • Thanks a lot! Works!!! - Andrey
  • @Andrey But it should not. - Shamov
  • So if AFTER insert, it first adds, and then removes the same record by the WHEN 'RINGNOANSWER' criterion, and not everything. - Andrey
  • And you can somehow do more elegantly than to delete after adding. How to ignore the criterion. Thank! - Andrey