Rusty Razor Blade
If 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.
If you enjoyed this article, please consider sharing it!
Good job! i always wanted to know how this stuff.
I’d love to see this article on http://www.eioba.com
i want to be reset a table at one hour (and a day) how can i do this
You can reset a table using cron jobs. i am assuming you are simply asking for truncating a table automatically.
MySQL Triggers Tutorial…
Jon is one of my developers at work. He recently started a WordPress blog called Rusty Razor Blade. I’m guessing that most of what he’ll be posting is going to be fairly technical stuff about MySQL, LAMP, AJAX, and JSON. So far his first…
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
You need to use
DELIMITER |
at the beginning, so the semicolons don’t end the statement.
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
“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.
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.
Thanks a lot!
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.
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
Triggers do not produce a response, or any have any output.
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`
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.
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!!….
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?
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
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!
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.
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.
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.
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();
how to trigger between two database??
Dio:
If you’re talking about 1 machine with multiple schemas: did you try prefixing the database name before your table?
If you’re talking about 2 different database servers, I don’t think you can do that with MySQL.
Hi, very good explanation of all the examples and theory you give to the folk. Thank you for the good job.
I have a question that I cannot resolve yet and I hope you can help me out.
I’m using MySQL 5.1.29 RC and I wrote a database with some tables one of them is called “users”. I want to create a trigger before insert on the table “login”. ie. before inserting any value in the table “login” I want to insert some values in another table called “users”, my code is below:
First I created my tables with the following syntax:
create users
(
userID int unsigned not null auto_increment primary key,
company char(50)
)type=innodb;
create login
(
userID int unsigned not null,
username char(100),
password char(100) not null unique
)type=innodb;
After That I created the trigger:
create triger test_trg before insert on login
for each row begin
insert into users values(NULL,’ ‘);
end;
result: it doesn’t work, I have tried using also those syntaxes:
create triger test_trg before insert on login
for each row begin
insert into users values(’0′,’ ‘);
end;
create triger test_trg before insert on login
for each row begin
insert into users values(0);
end;
create triger test_trg before insert on login
for each row begin
insert into users set userID = ’0′;
insert into users set company = ‘somecmpy’;
end;
create triger test_trg before insert on login
for each row begin
insert into users set userID = 0;
insert into users set company = ‘somecmpy’;
end;
Result: I couldn’t create my trigger yet.
Again:
I want to create a trigger before insert on the table “login”. ie. before inserting any value in the table “login” I want to insert a new value “userID” for the table “users”. Can you give me any clue about the problem. Thanks in advance.
I just followed above article and is help ful to me. I m first time using trigger, it is perfect article for a bigainer.
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 for SMS Gateway, I have table “inbox” and table “voting”, I want to input table “voting” automatictly whenever SMS come (store in table inbox)I try to creat script and try to sens messange like this “man 5 3 4 10 20 30 40 50 60 70 80 90 100 11 22″:
CREATE TRIGGER smsin AFTER INSERT ON inbox
FOR EACH ROW
$row = array(“TextDecoded”);
$cut=explode(” “,$row);
if ($cut[1]=’man’)
{
$query = mysql_query (“insert into voting1 values
(‘ ‘,’$cut1[0]‘,’$cut1[1]‘,’$cut1[2]‘,’$cut1[3]‘,’$cut1[4]‘,
‘$cut1[5]‘,’$cut1[6]‘,’$cut1[7]‘,’$cut1[8]‘,’$cut1[9]‘,
‘$cut1[10]‘,’$cut1[11]‘,’$cut1[12]‘,’$cut1[13]‘,’$cut1[14]‘,’$cut1[15]‘,’$cut1[16]‘)”);
}
else
{
$query = mysql_query (“insert into voting2 values
(‘ ‘,’$cut1[0]‘,’$cut1[1]‘,’$cut1[2]‘,’$cut1[3]‘,’$cut1[4]‘,
‘$cut1[5]‘,’$cut1[6]‘,’$cut1[7]‘,’$cut1[8]‘,’$cut1[9]‘,
‘$cut1[10]‘,’$cut1[11]‘,’$cut1[12]‘,’$cut1[13]‘,’$cut[14]‘)”);
}
Buat it didn’t run… could anybody can help ?
I Really appreciate.
Thanks all!
Hey, everyone
Can you help me?
I have problem. I have trigger that definer is my own create. When i disable my own create, my trigger is error. I want alter my trigger with other definer that I must not drop it then recreate.
[...] MySQL Triggers Tutorial, more detail explanation of mysql trigger line per line. [...]
hi. i have a question. how to ALTER TABLE in CREATE TRIGGER? i want to ADD ROW in TABLE
for example:
ALTER TABLE `faqs` ADD ‘question_’+NEW.code INT NOT NULL;
ALTER TABLE `faqs` ADD ‘answer_’+NEW.code INT NOT NULL;
Thanks for the tutorial. Haven’t worked much with triggers and this was helpful.
Thank you so much for explaining this, i never properly read the docs. Have been using oracle too much.
Hi There,
I am student , working out some examples on trigger. I have question , how to write a trigger for multiple events like INSERT and UPDATE togther.
For exmaple,
Write a trigger that will be fired whenever an insertion or update occurs on the EMPLOYEE
table?
Please help me out, I know how to write a Oracle code for this, but I need to know how it is done on MYSQL..
Thanks in advance!!
Regards,
Karuna
I’ve got a question for you on triggers. I need to do recipricol updates on two separate tables with the same field, update t1 with t2 changes and vice versa. MySQL is giving me an error saying I can’t update a table a change is being made on, presumably because it is engaging the t2′s trigger when a change is made to t1 to change t1 again, creating an infinite loop. Is this possible or am I missing something with the MySQL error?
thx
Hi Mark – as far as I know, there’s no way to do it.
I’ve got a question for you on triggers.Can you help me solve this?
create a trigger named invoices_before_payment for the Invoices table that raises an application error whenever the payment total plus credit total becomes larges than the invoice total in a row. Then, test this trigger with an appropriate UPDATE statement.(Note that you could code a check constraint to accomplish the same task.)
Thank you.
Hello,
I am trying to write a BEFORE row-level trigger for a table Product(ProductName, Price) and I’m using MySQL 5.0 for that. I basically need to ensure that if the user tries to insert a Product entry that has price greater than 3, the entry should not get inserted. I’m getting a syntax error. Can someone please help me fix it?
Here’s my query:
CREATE TRIGGER ExpensivePriceTrigger
AFTER INSERT ON Product
FOR EACH ROW
WHEN (new.Price > 3)
DELETE FROM Product
WHERE ProductName = new.ProductName
When I try to run it, it gives me this error:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHEN (new.Price > 3) DELETE FROM Product WHERE ProductName = new.ProductN’ at line 4
Thanks!
- Ekta
Hi,
I want to write trigger which will insert data into table2 after getting sqlcode 1062(duplicate entry) while inserting into table1.
I want to write this into mysql.
Please someone guide me.
I want to write trigger which will insert data into table2 after getting sqlcode 1062(duplicate entry) while inserting into table1.
I want to write this into mysql.
Please someone guide me.
Hi,
I have two databases called amir_ldap with a table called person and another database called amir_uldap with a table called person.
There tables have only two fields, firstname and lastname.
All I need is that when I add a record into amir_ldap it insert into amir_uldap.person too via a trigger.
I have written this but doesn’t work somehow:
CREATE TRIGGER amir_ldap AFTER INSERT ON amir_ldap.person FOR EACH ROW BEGIN INSERT INTO amir_uldap.person SET firstname = NEW.fname and lastname = NEW.lname;
appreciate your help.
Amir
A few months late, but answer for posterity…
@Amir
CREATE TRIGGER person_insert AFTER INSERT ON person
FOR EACH ROW BEGIN
INSERT INTO amir_uldap.person (fname,lastname) VALUES (NEW.fname,NEW.lastname);
END
Hi,
I have a table with 3 fields – 1stname, lastname and fullname. What I want: after the insert of lastname, a trigger to insert in fullname the contents of the first 2 fields.Ex: “John” (f1);”Scott” (f2); trigger – “John Scott” on (f3) automatically. Help please.
Thank you!
I had my hair being pulled out, and all it was it was a DELIMITER causing a problem!
DELIMITER $$
DROP TRIGGER `trigger_name`$$
create trigger `trigger_name` BEFORE UPDATE on `table_name`
for each row BEGIN
IF OLD.gid NEW.gid THEN
BEGIN
/* Make sure its a group associated with Shopper group – might not be*/
select count(*)
from A_table_name
where acl_group_id = NEW.gid
into @sync_required;
if ( @sync_required >= 1 ) then
BEGIN
/* Verify if the customer acc. already exists in VM’s table */
select count(*)
from A_table_name
where user_id = NEW.id
into @user_acc_exists;
if ( @user_acc_exists = 0 ) THEN
BEGIN
/* Insert new entry into jos_vm_user_info */
INSERT INTO a_table_name
(
user_info_id,
user_id,
address_type,
last_name,
first_name,
address_1,
city,
state,
country,
zip,
user_email,
cdate
)
VALUES(
md5(
date_format(
date_add( sysdate(), INTERVAL FLOOR( 1 + (RAND() * 998)) MICROSECOND),
“%Y%m%d%H%i%s%f”
)
),
NEW.id,
“BT”,
SUBSTRING( NEW.name, (LENGTH( NEW.name ) – LOCATE(‘ ‘, REVERSE( NEW.name ) ) )+2),
left(NEW.name, INSTR(NEW.name,’ ‘)),
NEW.email,
UNIX_TIMESTAMP()
);
/* Insert Entry into a_table_name */
INSERT INTO a_table_name
(
user_id,
vendor_id,
shopper_group_id
)
SELECT NEW.id,
1,
b.vm_shopper_group
FROM a_table_name b
WHERE b.acl_group_id = NEW.gid;
END;
ELSE /* User acc in VM already exists*/
BEGIN
/* Since the customer already exists, we presume entry in
jos_vm_shopper_vendor_xref also exists, so just need to
update to the shopper group associated with the ACL Group
*/
SELECT b.vm_shopper_group
FROM a_table_name b
WHERE b.acl_group_id = NEW.gid
INTO @updated_shopper_group;
UPDATE a_table_name
SET shopper_group_id = @updated_shopper_group
WHERE user_id = NEW.id;
END;
END IF; /* ( @user_acc_exists = 0 ) */
END;
END IF; /* (sync >= 1 ) */
END;
END IF; /* OLD.gid NEW.gid */
END;
$$
DELIMITER ;
Hello, I have some errors in my syntax trigger. Please help me to fix this error
DELIMITER $$
DROP TRIGGER `trigger_name`$$
create trigger `trigger_name` BEFORE UPDATE on `table_name`
for each row BEGIN
IF OLD.gid NEW.gid THEN
BEGIN
/* Make sure its a group associated with Shopper group – might not be*/
select count(*)
from A_table_name
where acl_group_id = NEW.gid
into @sync_required;
if ( @sync_required >= 1 ) then
BEGIN
/* Verify if the customer acc. already exists in VM’s table */
select count(*)
from A_table_name
where user_id = NEW.id
into @user_acc_exists;
if ( @user_acc_exists = 0 ) THEN
BEGIN
/* Insert new entry into jos_vm_user_info */
INSERT INTO a_table_name
(
user_info_id,
user_id,
address_type,
last_name,
first_name,
address_1,
city,
state,
country,
zip,
user_email,
cdate
)
VALUES(
md5(
date_format(
date_add( sysdate(), INTERVAL FLOOR( 1 + (RAND() * 998)) MICROSECOND),
“%Y%m%d%H%i%s%f”
)
),
NEW.id,
“BT”,
SUBSTRING( NEW.name, (LENGTH( NEW.name ) – LOCATE(‘ ‘, REVERSE( NEW.name ) ) )+2),
left(NEW.name, INSTR(NEW.name,’ ‘)),
NEW.email,
UNIX_TIMESTAMP()
);
/* Insert Entry into a_table_name */
INSERT INTO a_table_name
(
user_id,
vendor_id,
shopper_group_id
)
SELECT NEW.id,
1,
b.vm_shopper_group
FROM a_table_name b
WHERE b.acl_group_id = NEW.gid;
END;
ELSE /* User acc in VM already exists*/
BEGIN
/* Since the customer already exists, we presume entry in
jos_vm_shopper_vendor_xref also exists, so just need to
update to the shopper group associated with the ACL Group
*/
SELECT b.vm_shopper_group
FROM a_table_name b
WHERE b.acl_group_id = NEW.gid
INTO @updated_shopper_group;
UPDATE a_table_name
SET shopper_group_id = @updated_shopper_group
WHERE user_id = NEW.id;
END;
END IF; /* ( @user_acc_exists = 0 ) */
END;
END IF; /* (sync >= 1 ) */
END;
END IF; /* OLD.gid NEW.gid */
END;
$$
DELIMITER ;
CREATE TRIGGER `test_tbl_before_insert`
BEFORE INSERT ON `MyTable`
FOR EACH ROW
BEGIN
select * from MyTable
END;
mysql version 5.5.8
Hello Sir,
MySelf Ashish from India but last 8 months i was belong from Newyork with my brother.
I want some help about trigger.
First is , I have create php script with database connection and insert some data in first table and another table i was create also.
Plesae tell me how to execute the trigger and can we write in php variable like as $str = “create trigger ….”;
and most important is how to execute it and execute by itself or not ?.
and is it must trigger with mysqli extension ?.
Hi Sir i have table like Contact
Con_ID CON_Name MMS_ID
1 A MMS001
whan am inserting 2nd record the MMS_ID genrate automatically sequence number Like MMS002
please write a query on Trigger