MySQL triggers and stored procedures


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”

Leave a Reply

Your email address will not be published. Required fields are marked *