MySQL 5.0 triggers

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 <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, 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 <trigger Name>
{Before | after} <--
{Insert | update | Delete}
On <Table Name>
For each row
<SQL statement triggered>

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

3. Syntax: Event
Create trigger <trigger Name>
{Before | after}
{Insert | update | Delete} <--
On <Table Name>
For each row
<SQL statement triggered>

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

4. Syntax: Table
Create trigger <trigger Name>
{Before | after}
{Insert | update | Delete}
On <Table Name> <--
For each row
<SQL statement triggered>

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 <trigger Name>
{Before | after}
{Insert | update | Delete}
On <Table Name>
For each row <--
<SQL statement triggered>

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 <trigger Name>
{Before | after}
{Insert | update | Delete}
On <Table Name>
For each row
<SQL statement triggered> <--

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 <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>;

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 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 '*///

Bugs

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 <Table Name>. <trigger Name> ".
The correct way is "Drop trigger <trigger Name> ".
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.
Last conclusion

At the end of the book, I don't think you need to review it or review it, because I believe you can easily remember what we mentioned above.
Author: MySQL AB; Translation: Chen Pengyi