I am trying to get this mysql trigger to work in mysql workbench. It will happily tell me when there is an error, but the minute everything appears ok it doesn't run. I've run a show triggers query and nothing is returned. Running v8.0.28.

delimiter //
CREATE TRIGGER add_job_item
AFTER INSERT ON estimate_line
FOR EACH ROW
BEGIN
    IF (NEW.CoreTypeID = 3 AND NEW.CoreResourceID IS NOT NULL) THEN BEGIN
        INSERT INTO job_items (EstimateLineID) VALUES (NEW.EstimateLineID);
    END; # END IF; here doesn't work
END;// # I have tried END; END;//

delimiter ;

Oddly (and I've left it here) the ;// is actually given as an example on the Mysql documentation but errors when I run it (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html).

CodePudding user response：

I tested this on MySQL 8.0.31 and it works:

delimiter //
CREATE TRIGGER add_job_item
AFTER INSERT ON estimate_line
FOR EACH ROW
BEGIN
    IF (NEW.CoreTypeID = 3 AND NEW.CoreResourceID IS NOT NULL) THEN
        INSERT INTO job_items (EstimateLineID) VALUES (NEW.EstimateLineID);
    END IF;
END//

delimiter ;

Differences:

• IF must be ended by END IF;.
• You don't need a BEGIN...END within an IF block (unless you need to use DECLARE in the block). It's already a compound statement, and accepts a list of statements in the block.
• You do need to use the current delimiter after the last END. That is, END// in this example.