So MySQL is trying to be a big boy and have advanced features like triggers and stored procedures (not just UDF’s). However their syntax seems a little complicated compared to the PostgreSQL one. So here it goes:
DROP TRIGGER IF EXISTS mytrigger; DELIMITER | CREATE TRIGGER mytrigger BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | DELIMITER ;
The play with the delimiter is necessary to be able to put multiple statements (separated by 😉 inside of the trigger. The
DROP TRIGGER IF EXISTS construct is the equivalent of the
CREATE OR REPLACE construct from PostgreSQL.
The syntax for procedures / functions is similar:
DROP PROCEDURE IF EXISTS simpleproc; DELIMITER // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END; //
One response to “MySQL triggers and stored procedures”
thanks for the examples.
it’s been a quick and easy introduction into triggers for me…