MySQL5.0-trigger (reference) _ MySQL

ConventionsandStyles convention and programming style 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 (let everyone distinguish between program code and body ). Here is an example: mysqlDROPFUNCTIONf; QueryOK, 0 rowsaffecte Conventions and Styles 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 (let everyone distinguish between program code and body ). 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 (if you are not reading an electronic version, 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 does Why Triggers use Triggers?

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.

The Flow-of-control statement (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) is 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 DELETE

DROP 'flush privileges' GRANT INSERT KILL

LOCK OPTIMIZE REPAIR REPLACE REVOKE

Rollback savepoint 'Select FROM table'

'Set system variable' 'set transaction'

SHOW 'Start transaction' TRUNCATE UPDATE

The trigger also has the same restrictions.

The trigger is relatively new, so there will be (bugs) defects. 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: Name Syntax: naming rules

CREATE TRIGGER <触发器名称> <--
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE}
ON <表名称>
FOR EACH ROW
<触发器sql语句>

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, the trigger is BEFORE the event UPDATE (see the following points (2) and (3) (BEFORE), then its name is t26_bu.

2. Syntax: Time Syntax: Trigger Time

CREATE TRIGGER <触发器名称>
{BEFORE | AFTER} <--
{INSERT | UPDATE | DELETE}
ON <表名称>
FOR EACH ROW
<触发的sql语句>

Trigger execution time settings: you can set the time before or after an event occurs.

3. Syntax: Event

CREATE TRIGGER <触发器名称>
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE} <--
ON <表名称>
FOR EACH ROW
<触发的sql语句>

The trigger events can also be set: they can be triggered during the execution of insert, update, or delete.

4. Syntax: Table

CREATE TRIGGER <触发器名称>
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE}
ON <表名称> <--
FOR EACH ROW
<触发的sql语句>

A trigger belongs to a table. When an 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: Granularity Syntax :( step) trigger interval

CREATE TRIGGER <触发器名称>
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE}
ON <表名称>
For each row <--
<触发的sql语句>

Trigger execution interval: the for each row clause notifies the trigger to execute an action on every ROW instead of the entire table.

6. Syntax: Statement

CREATE TRIGGER <触发器名称>
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE}
ON <表名称>
FOR EACH ROW
<触发的sql语句> <--

The trigger contains the SQL statement to be triggered: 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 permission

You must have considerable permissions to CREATE a TRIGGER ). If you are a Root user, it is enough. This is different from the SQL standard. I also hope to change it to the standard as soon as possible.

Therefore, in the next MySQL version, you may see a new permission called create trigger. Then, use the following method to Grant:

GRANT CREATE TRIGGER ON <表名称> TO <用户或用户列表> ;

You can also revoke permissions as follows:

REVOKE CREATE TRIGGER ON <表名称> FROM <用户或用户列表> ;

Referring to OLD and NEW columns about the OLD and newly created column identifiers

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 (this 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 identify:

"NEW. column_name" or "OLD. column_name". Technically, the NEW (NEW | OLD. column_name) 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 t20000au
Before update on t22
FOR EACH ROW
BEGIN
SET @ 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.

Example of CREATE and INSERT

CREATE table with trigger

In all the examples, I assume that the DELIMITER has been set to // (DELIMITER //).

Create table t22 (s1 INTEGER )//

Create trigger t22_bi
Before insert on t22
FOR EACH ROW
BEGIN
SET @ x = 'triggers 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.

INSERT on table w ith a trigger

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.

Example of a "check" constraint
"Check" integrity constraints

What's a "check" constraint What is a "check" constraint

In the standard SQL language, we can use "CHECK (condition )",
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_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 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 '000000 '*///

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 statement about the trigger error, but there are still many old manuals on the internet. 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.

Bugs
(If something is bad, it will not be translated)
On December 14 2004, I did an "Advanced Search" in http://bugs.mysql.com for 'trigger' or
'Trigger', 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 havent, I'll mention the important ones. If they're still
There, you'll have to work around und them when you're trying triggers.

Bug #5859 drop table does not drop triggers.
(The trigger is not automatically deleted when the table is deleted)
When you drop a table, dropping the table's triggers shoshould be automatic IC.

Bug #5892 Triggers have the wrong namespace.
(The trigger namespace is incorrect. you must add the table name before deleting the trigger. The following is an example)
You have to say "DROP TRIGGER

".
The correct way is "DROP TRIGGER ".

Bug #5894 Triggers with altered tables cause failed UPT databases.
(Changes to the trigger table may cause damage to the database data)
Do not alter a table that has a trigger on it, until you know this is fixed.

.