MySQL 5.0 triggers reference page 1/4

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.