MySQL – triggers

In MySQL version 5 onwards you can use Triggers, triggers are a nice was of checking values within a insert/update/delete process. A trigger happens either just before the insert actually happens into the database, or just after

Lets say that you create a table as below

CREATE TABLE testTable 
(id INT NOT NULL AUTO_INCREMENT, 
  guid VARCHAR(36), 
  name VARCHAR(100), 
PRIMARY KEY (id));

and then if you want to insert a name into the name column you could do

INSERT INTO testTable (name) VALUES ("thename");

but you are not inserting the anything into the guid column because you can force a value into that column with a trigger (the id is already creating a value with the primary key, auto_increment).

To create a trigger you need to set the delimiter to “//” because within sql you need to use the “;” which is the standard delimiter (when the MySQL will try and execute the query)

delimiter //
CREATE TRIGGER testTable_insert 
BEFORE INSERT ON 'testTable' 
FOR EACH ROW 
BEGIN 
    SET NEW.guid = uuid(); 
END;
delimiter ;

“CREATE TRIGGER” is just like creating a table, apart from you are creating a trigger, so the next value is the trigger name, normally it is the table name with what you are doing, e.g. inserting

“BEFORE INSERT ON” means before you actually insert the data into the database (it is in a hold area as such). the next value is the actual table that you are linking this trigger to.

“FOR EACH ROW” means each row of the insert, since you can insert x amount of lines into a table.

the sql code is between the “BEGIN” and “END”, and all is what is being set is the guid column within the “NEW” (this is the new row to be inserted) and setting that value to uuid() which is a mysql function.

so before you would have inserted into a table and the table would have looked like

ID GUID Name
1 thename

but once the trigger is in place and you did the same process again of inserting a new name

INSERT INTO testTable (name) VALUES ("a new name");
ID GUID Name
1 thename
1 3ace82c-2cf1-11df-b1c3-00a0d1a1240a a new name

2 thoughts on “MySQL – triggers”

  1. A mysql trigger is applied on the database, so it is a database code, the php code would “use” the trigger when it does a insert but you do not code a mysql trigger as such from php.

Leave a Reply

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