MySQL5 trigger tutorial

Conventions and programming styles
Every time I want to demonstrate the actual code, I will adjust the code displayed on the screen of the mysql client and change the font to Courier, make them look different from common text so that you can differentiate program code and text ). Here is an example:

mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)

If the instance is large, you need to add comments between some rows and paragraphs, and I will place the "<--" symbol on the right side of the page to emphasize. For example:

mysql> CREATE PROCEDURE p ()-> BEGIN-> /* This procedure does nothing */ <---> END;//Query OK, 0 rows affected (0.00 sec)

Sometimes I will remove the "mysql>" and "->" systems in the example. You can copy the Code directly to the mysql client program, you can download related scripts on the mysql.com website. Therefore, all the examples have passed the test on Suse 9.2 Linux and Mysql 5.0.3 public edition. When you read this book, Mysql has a higher version and supports more operating systems, including Windows, Linux, and HP-UX. So the example here will run normally on your computer. However, if the operation still fails, you can consult a senior Mysql user you know so that you can get better support and help.
Why use a trigger?
The reason why we support triggers in MySQL 5.0 is as follows:

Users of earlier MySQL versions have long-term requirements for triggers. We have promised to support all ANSI-standard features. You can use it to check or prevent bad data from entering the database. You can change or cancel INSERT, UPDATE, and DELETE statements. You can monitor data changes in a session.

Here, I assume that everyone has read "MySQL new feature" series's first episode-"MySQL Stored Procedures", so everyone should know that MySQL stored procedures and functions are very important, because you can use the statements used in functions in triggers. For example:

The compound statement (BEGIN/END) is valid. flow control Flow-of-control) Statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are also valid. variable Declaration (DECLARE) and assignment (SET) are legal. allow condition declaration. exception Handling statement is also allowed. however, remember that a function has a restriction: you cannot access a table in the function. therefore, the following statement is invalid in the function. ALTER 'cache Index' call commit create deletedrou' flush privileges 'Grant insert killlock optimize repair replace revokerollback savepoint 'select FROM table' 'set system variable' 'set transaction' SHOW 'start transaction' truncate update has the same limits in triggers.

The trigger is relatively new, so there will be a bugs) defect. so I am here to give you a warning, as I said in the stored procedure book. do not use this trigger in databases that contain important data. If necessary, use the trigger in some test-oriented databases and confirm that these databases are default when you create a trigger on a table.
Syntax

1. Syntax: naming rule create trigger <TRIGGER Name> <-- {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <trigger SQL statement>

The trigger must have a name and a maximum of 64 characters. It may be followed by a separator. It is similar to the naming method of other objects in MySQL.
Here I have a habit: using the table name + '_' + the abbreviation of the trigger type. therefore, for table t26, if the trigger is BEFORE event UPDATE, its name is t26_bu.

2. syntax: TRIGGER time: create trigger <TRIGGER Name> {BEFORE | AFTER} <-- {INSERT | UPDATE | DELETE} ON <Table Name> for each row <triggered SQL statement> TRIGGER executed time Setting: it can be set to before 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> event: they can be triggered when an insert, update, or delete operation is executed. 4. syntax: table create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> <-- for each row <triggered SQL statement> the TRIGGER belongs to table: when the insert, update, or delete operation is performed on the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table. 5. syntax: Step Size) TRIGGER interval create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <-- <triggered SQL statement> TRIGGER execution Interval: the for each row clause notifies the trigger to execute an action every ROW instead of the entire table. 6. syntax: statement create trigger <TRIGGER Name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <Table Name> for each row <triggered SQL statement> <-- TRIGGER contains SQL statement: the statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as the functions. Privileges permissions: You must have considerable permissions to CREATE the trigger create trigger ). If you are a Root user, it is enough. This is different from the SQL standard. Therefore, in the next MySQL version, you may see a new permission called create trigger. GRANT the following permissions to grant: grant create trigger on <Table Name> TO <user or user list>; you can also revoke permissions as follows: revoke create trigger on <Table Name> FROM <user or user list>;

ID of the old and newly created Columns
In the SQL statement of the trigger, you can associate any column in the table. However, you cannot only use the column name to identify it, which will confuse the system, because there may be a new column name, which may be exactly what you want to modify, your action may be to modify the column name), and the old name of the Column exists. Therefore, you must use this syntax to mark: "NEW. column_name "or" OLD. column_name ". in this way, NEW | OLD is processed technically. column_name) the New and Old column names belong to the created transition variable "transition variables ").
For INSERT statements, only NEW statements are valid; For DELETE statements, only OLD statements are valid; and UPDATE statements can be used together with NEW and OLD statements. The following is an example of using both NEW and OLD in UPDATE.

Create trigger t21_auBEFORE update on t22FOR each rowbeginset @ old = OLD. s1; SET @ new = NEW. s1; END; // if the value of column s1 in Table t21 is 55, the value of @ old is 55 after "UPDATE t21 SET s1 = s1 + 1" is executed, the value of @ new will change to 56.

The Example of CREATE and INSERT creates a table with a trigger. In all the examples, I assume that the DELIMITER has been set to // DELIMITER //). Create table t22 (s1 INTEGER) // create trigger t22_biBEFORE insert on t22FOR each rowbeginset @ x = 'trigger was activated! '; Set new. s1 = 55; END ;//

At the beginning, I created a table named t22, and then created a trigger t22_bi on table t22. When we want to insert rows into the table, the trigger will be activated, change the value of column s1 to 55.
Use a trigger to execute the insert action
Mysql> insert into t22 VALUES (1 )//
Let's see what happens if we insert a data trigger row to table t2? The insert action here is very common. We do not need the trigger permission to execute it. You do not even need to know whether a trigger is associated.

mysql> SELECT @x, t22.* FROM t22//+------------------------+------+| @x | s1 |+------------------------+------+| Trigger was activated! | 55 |+------------------------+------+1 row in set (0.00 sec)

We can see the results after the INSERT action. As we expected, the x mark is changed. At the same time, the inserted data is not the data we started to INSERT, but the data of the trigger.

"Check" integrity constraints example what is "check" constraints in the standard SQL language, we can use "CHECK (condition)" when creating a TABLE in CREATE TABLE, for example: create table t25 (s1 INT, s2 CHAR (5), primary key (s1), CHECK (LEFT (s2, 1) = 'A') ENGINE = INNODB;

CHECK indicates that "when the leftmost character of column s2 is not 'a', the insert and update statements are invalid." MySQL views do not support CHECK, I personally hope it can be supported. However, if you need to use such a function in a table, we recommend that you use a trigger.

CREATE TABLE t25(s1 INT, s2 CHAR(5),PRIMARY KEY (s1))ENGINE=INNODB//CREATE TRIGGER t25_biBEFORE INSERT ON t25FOR EACH ROWIF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//CREATE TRIGGER t25_buBEFORE UPDATE ON t25FOR EACH ROWIF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

I only need to use the BEFORE INSERT and BEFORE UPDATE statements. Deleting a trigger does not affect the table, and the AFTER trigger cannot modify the NEW procedure variable transition variables ). To activate the trigger, I inserted data s1 = 0 to the rows in the table. After that, all operations that meet the LEFT (s2, 1) <> 'A' condition will fail:

INSERT INTO t25 VALUES (0,'a') /* priming the pump */ //INSERT INTO t25 VALUES (5,'b') /* gets error '23000' */ //Don't Believe The Old MySQL Manual

It's time to discard the old MySQL manual.
I am here to warn you not to trust what was mentioned in the MySQL manual in the past. We have removed the wrong statement about the trigger, but there are still a lot of old manuals online, for example, this is a German Url: http://dev.mysql.com/doc/mysql/de/ANSI_diff_Triggers.html. in this manual, a trigger is a stored procedure. Forget it. You can see that a trigger is a trigger, and a stored procedure is a stored procedure. The Manual also says that triggers can be deleted from other tables, or triggered when you delete a transaction. No matter what he means, forget it. MySQL will not implement this. Finally, it is wrong to say that using a trigger will affect the query speed, and the trigger will not affect the query.