A trigger is a special kind of
stored procedure that automatically executes when an event occurs in the
database server. MySQL triggers is one of the best features of an enterprise
database server it is first implement in version 5.0.2.
Do not try triggers with a database
that has important data in it. Instead, create a new database for testing
purposes, and make sure this test database is the default, whenever you are
creating or using tables with triggers.
Syntax
1. Syntax: Name
CREATE TRIGGER <trigger
name>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <table name> <--
FOR EACH ROW
<triggered SQL statement>
àTriggers
must have names, up to 64 characters long, possibly enclosed in backtick
`delimiters`. In other words, they're much like names of other objects in
MySQL.
2. Syntax: Time
CREATE TRIGGER <trigger name>
{ BEFORE |
AFTER }
{ INSERT | UPDATE | DELETE }
ON <table name> <--
FOR EACH ROW
<triggered SQL statement>
àTriggers have an action time: they can be
activated before an event or after an event.
3. Syntax:
Event
CREATE
TRIGGER <trigger name>
{
BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON
<table name> <--
FOR
EACH ROW
<triggered
SQL statement>
àTriggers
also have an event: they can be activated during insert, or during update, or
during delete.
4. Syntax: Table
CREATE TRIGGER <trigger name>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON
<table name>
FOR EACH ROW
<triggered SQL statement>
àTriggers have a subject
table: it is inserts or updates or deletes on this subject table that cause
trigger activation. I can't make two triggers for the same table and the same
event.
5. Syntax:
Granularity
CREATE TRIGGER <trigger
name>
{ BEFORE | AFTER }
{ INSERT | UPDATE |
DELETE }
ON <table
name>
FOR EACH ROW
<triggered SQL
statement>
àTriggers have a
granularity: the FOR EACH ROW clause says that trigger activation will occur
for the rows of the table, not for the table as a whole.
6. Syntax:
Statement
CREATE TRIGGER
<trigger name>
{ BEFORE | AFTER }
{ INSERT | UPDATE |
DELETE }
ON <table
name>
FOR EACH ROW
<triggered SQL statement>
àTriggers have a
triggered SQL statement: the statement can be any statement, including a
compound statement, but triggers have all the same limitations as functions.
Privileges
In orther to get the SUPER privilege for CREATE TRIGGER
you must be in root.
Example from:
A MySQL® Technical White Paper
Peter Gulutzan
March, 2005
CREATE table with trigger
For this and all examples, I assume that the delimiter
has already been set, with
DELIMITER //
CREATE TABLE t22 (s1 INTEGER)//
CREATE TRIGGER t22_bi
BEFORE INSERT ON t22
FOR EACH ROW
BEGIN
SET @x = 'Trigger was activated!';
SET NEW.s1 = 55;
END;//
I begin by creating a test table named t22. Then I
create a trigger on table t22, so that before I insert any row into the table,
I set a flag that the trigger was activated, and I change the new value of a
column in the table to fifty-five.
INSERT on table with a trigger
mysql> INSERT INTO t22 VALUES (1)//
Let's see what happens if I insert a row into table t2,
the trigger's subject table.
The INSERT statement is utterly ordinary. I do not need
to have “privileges on the trigger”. I do not even need to know that the
trigger exists.
mysql> SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)
Here we see what happens as a result of the INSERT. The
x flag is set, as expected. And the value that actually gets inserted isn't
what I said to INSERT in the INSERT statement. Instead, it's what I said to
INSERT in the trigger.
Example of a “check” constraint
What's a “check” constraint
In standard SQL, one can say “CHECK (condition)” as
part of a CREATE TABLE statement, for example
CREATE TABLE t25
(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;
The CHECK means
“insert and update are illegal unless the leftmost character of column s2 is
'A'”. MySQL doesn't support that. MySQL does support the CHECK clause for
views, which is what I'd recommend. But just in case you insist on putting the
equivalent of a CHECK clause on a base table, here's how you can do it with
triggers.
CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//
CREATE TRIGGER
t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF
LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//
CREATE TRIGGER
t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF
LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//
I only need BEFORE
INSERT and BEFORE UPDATE triggers. DELETE triggers won't matter, and AFTER
triggers can't change NEW transition variables.
I have to prime the
pump by inserting a row with s1=0. After that, any attempt to add or change so
that LEFT(s2,1) <> 'A' will fail. Try it with:
INSERT INTO t25
VALUES (0,'a') /* priming the pump */ //
INSERT INTO t25
VALUES (5,'b') /* gets error '23000' */ //
Base on PDF
mysql-triggers by
A MySQL® Technical White Paper
Peter Gulutzan
March, 2005
Bugs
On December 14 2004,
I did an “Advanced Search” in http://bugs.mysql.com for 'trigger' or
'triggers', I found that there were 17 active bugs as of that date. Of course
they might disappear before you read this, but just in case they haven't, I'll
mention the important ones. If they're still there, you'll have to work around
them when you're trying triggers.
Bug#5859 DROP TABLE does not drop triggers.
When you drop a
table, dropping the table's triggers should be automatic.
Bug#5892 Triggers have the wrong namespace.
You have to say
“DROP TRIGGER <table name> . <trigger name>”.
The correct way is
“DROP TRIGGER <trigger name>”.
Bug#5894 Triggers with altered tables cause corrupt databases.
Do not alter a table
that has a trigger on it, until you know this is fixed.
For more information
please visit: www.mysql.com.
