MySQL Triggers Tutorial
September 14, 2006 – 2:34 pmIf you’ve worked with any of the commerical databases other than MySQL, you may have already had some experience with triggers.
I’m glad that the MySQL developers finally managed to squeeze in this extremely important feature. I no longer dread trying to defend MySQL from the Microsoft and Oracle developers who have had triggers for so many years.
I’ll use the example off the MySQL website (explained below in detail). I’ll assume you know how to create a table. If not, please read up on that first.
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);DELIMITER |
CREATE TRIGGER testref 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 ;
First, we need to change the end of line delimiter. We do this because the statements in the trigger need to end in a semicolon, this is explained further below. This is simple:
DELIMITER |
Next, we specify the action and table for this trigger. In this example, the table is “test1″, and the trigger will execute before any inserts.
CREATE TRIGGER testref BEFORE INSERT ON test1
We specify the following line to make sure if we’re doing a batch update it’ll apply the trigger to each row inserted.
FOR EACH ROW BEGIN
Now we can specify the actions that will occur as the trigger. We use the keywords NEW and OLD to reference the columns in the row. On an INSERT, there are no OLD columns.
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
We’re done - end the trigger. To finish this single statement (because this entire trigger is really 1 statement, remember we changed our end of statement delimiter) we end it with the |
END;
|
We can now change back the end of line delimiter to a semi-colon.
DELIMITER ;
You will now have a trigger on the table ‘test1′ that occurs on every insert.
A few notes:
As of MySQL 5, triggers on DELETES will not run if you delete via cascading foreign keys.
On an INSERT, you can only use NEW for column values. On a DELETE, you must use OLD.
I will post another tutorial soon on the practical use of triggers in web development. I hope this helps someone.
24 Responses to “MySQL Triggers Tutorial”
Good job! i always wanted to know how this stuff.
By Sunny on Sep 15, 2006
I’d love to see this article on http://www.eioba.com
By Riki on Sep 19, 2006
i want to be reset a table at one hour (and a day) how can i do this
By fatih akkoyun on Sep 19, 2006
You can reset a table using cron jobs. i am assuming you are simply asking for truncating a table automatically.
By Sunny on Sep 19, 2006
its not working for me, when i place semicolin at the end of statment it terminates the mysql prompt giving error. mysql version 5.0.18. cam someone help
By mateen on Oct 2, 2006
You need to use
DELIMITER |
at the beginning, so the semicolons don’t end the statement.
By jon on Oct 2, 2006
yes i want this –>truncating a table automatically
trigger can’t do or i can’t did with trigger
so what is the cron jobs
By fatih akkoyun on Oct 12, 2006
“I’m glad that the MySQL developers finally managed to squeeze in this extremely important feature. I no longer dread trying to defend MySQL from the Microsoft and Oracle developers who have had triggers for so many years.”
Yes triggers are nice. But if you dont know what you are doing, you can cause MAJOR overhead on your server. Ive been working with mysql 3.23.55 for a while (based on our business model upgrading is a slow process). So when you are trying to defend mysql from the evil empires, Keep in mind that they are trying to sell more licenses for servers and they will always tell you to use them.
By Chuck on Jan 2, 2007
Hi,
I created a create database script with create table and lots of insert statements inside. At the end of the file I added the create procedure and create trigger statements using the delimiter $$ statement, but when I try to run the batch with mysql -uroot -pxxx -e “source createdb.sql”, the batch fails at the first delimiter statement. Also want to mention, that the statements are working from mysql monitor client.
If I dump my database with triggers and strored procedures it will be filled with a lot of /*50003 CREATE */ style comments, which are working but makes the code unreadable and hard to maintain.
If you know how to include the Create Trigger statements into a large sql file and make them work, please let me know.
Thanks.
By Csaba on Feb 28, 2007
Thanks a lot!
By JP on Mar 21, 2007
I understand the basics of triggers, but is this a certain programming language? I would like to know what syntax the triggers use so I can make more complex triggers.
By Travis on Mar 22, 2007
hi there.will like to know how to capture the response, using php,generated by the triggers.is there exists such a way because most of the time we dont connect directly but using some other language
By ashu on Apr 22, 2007
Triggers do not produce a response, or any have any output.
By jon on Jun 3, 2007
Jon, Ashu,
Triggers can be used to generate an output. You can include a SELECT statment in your trigger, either as a simple string
SELECT “Task Completed”;
or a more complex table query
SELECT * FROM `my_tbl` WHERE `my_tbl.date`
By Dan on Jun 5, 2007
Be careful. Adding triggers in MySQL (as of 5.1) causes huge overhead and will slow performance to a point that is untenable for most moderate traffic servers. Having a feature is one thing, having a feature that performs like other DBs is quite another.
By Erick on Aug 9, 2007
thank u …thank u for letting me know the basics of triggers…regarding their creation…..syntax….working and all…..looking forward for another tutorial post!!….
By Sri harsha on Oct 5, 2007
I am new to triggers and get the syntax but have a hard time applying this knowledge!!
I have a an auction database, and i need to write a trigger that verifies that the next bid placed is larger than the current high bid. Can anyone help me?
By Ewa on Nov 9, 2007
Hey Ewa:
You’re probably going to want to use a stored procedure to check that. According to the MySQL boards, there’s no clean way to abort a query in mysql using a trigger. Read through this thread for more info:
http://forums.mysql.com/read.php?99,55108,55108#msg-55108
By jon on Dec 2, 2007
Hey Everyone…
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot!
Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated to “user”.
How do I write this trigger? I know, it’s anti-normalization, but it will really help me out in the long run.
Thanks all!
By Ryan on Dec 20, 2007
Hey Ryan
In this case, I’d recommend calling a stored procedure to perform this type of replication.
– almost identical tables. copying data from user to users
create table user (userid int auto_increment primary key, fname varchar(10), lname varchar(10));
create table users (userid int primary key, fname varchar(10), lname varchar(10));
delimiter |
create procedure copy_row(uid int)
begin
INSERT INTO users (userid, fname, lname)
SELECT userid, fname, lname from user WHERE userid = uid;
end;
|
delimiter ;
mysql> insert into user (fname, lname) values (’joe’, ’smith’), (’steve’, ‘brown’),(’jon’, ‘haddad’);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> call copy_row(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+——–+——-+——–+
| userid | fname | lname |
+——–+——-+——–+
| 1 | joe | smith |
| 2 | steve | brown |
| 3 | jon | haddad |
+——–+——-+——–+
3 rows in set (0.00 sec)
mysql> select * from users;
+——–+——-+——-+
| userid | fname | lname |
+——–+——-+——-+
| 1 | joe | smith |
+——–+——-+——-+
1 row in set (0.00 sec)
If you really want to do this with a trigger, you can do something like this:
delimiter |
create trigger user_ins AFTER insert on user
for each row begin
call copy_row(NEW.userid);
end;
|
create trigger user_upd AFTER update on user
for each row begin
call copy_row(NEW.userid);
end;
|
create trigger user_del AFTER delete on user
for each row begin
delete from users WHERE userid = OLD.userid;
end;
|
delimiter ;
mysql> insert into user (fname, lname) values (’octavious’, ‘jones’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+——–+———–+——-+
| userid | fname | lname |
+——–+———–+——-+
| 1 | joe | smith |
| 4 | octavious | jones |
+——–+———–+——-+
2 rows in set (0.00 sec)
Hopefully this helps.
By jon on Dec 21, 2007
Is it possible to generate emails with triggers? for example if a user is added to a database that the admin gets an email with the userdata? (I know this can easily be done with PHP, but I want to know if it can be done with triggers in MySQL.
By Raymond on Apr 9, 2008
To my knowledge, MySQL cannot send out emails. This question was brought up on the MySQL list in 2005, found here: http://forums.mysql.com/read.php?99,33635,33651#msg-33651
I believe it is possible in Postgres.
By jon on Apr 11, 2008
mysql> delimiter ;
mysql> delimiter //
mysql> create procedure mostrar1(prov varchar(15))
-> begin
-> select p.id_producto,p.des_producto,pr.des_proveedor
-> from tb_producto p,tb_proveedor pr where p.id_proveedor=pr.id_proveedor;
-> end
-> //
delimiter ;
call mostrar1();
By ketty on Jun 13, 2008