MY SQL TRIGGERS

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.