Back to Top

Tuesday, July 31, 2007

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;
//

1 comment:

  1. thanks for the examples.
    it's been a quick and easy introduction into triggers for me...

    ReplyDelete