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
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");
|1||3ace82c-2cf1-11df-b1c3-00a0d1a1240a||a new name|
2 thoughts on “MySQL – triggers”
can i insert the mysql trigger into php code?
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.