MySQL 5.0-Triggers (reference)

Conventions and styles conventions and programming styles

Every time I want to demonstrate the actual code, I adjust the code that appears on the MySQL client's screen, changing the font to courier so that they look different from normal text (which lets you distinguish between the program code and the body). Here's an example:

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

If the instance is larger, you need to annotate some lines and paragraphs, and I will use 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, and you can copy the code directly to the MySQL client (if you are not reading the electronic version, you can download the relevant script on the MySQL.com website)

So the examples have been tested in the SuSE 9.2 Linux, Mysql 5.0.3 public edition. As you read this book, MySQL already has a higher version and can support more OS, including Windows,sparc,hp-ux. So the example here will be able to run normally on your computer. However, if the operation still fails, you can consult the senior MySQL users you know, so that you can get better support and help.

Why triggers Why do you use triggers

We included support for triggers in MySQL 5.0 for the following reasons:

MySQL's earlier version of the user needs a trigger request for a long time.

We have promised to support the attributes of all ANSI standards.

You can use it to check or prevent bad data from entering the database.

You can change or cancel the INSERT, UPDATE, and DELETE statements.

You can monitor the movement of data changes in a session.

Here I assume that everyone has read the first episode of the MySQL new feature series, "MySQL stored procedures," so everyone should know MySQL to this stored procedure and function, which is very important knowledge, because in the trigger you can use the statement used in the function. In particular, give an example:

Compound statements (begin/end) are legal.

Flow control (Flow-of-control) statements (IF, case, while, loop, while, REPEAT, leave,iterate) are also legal.

Variable declarations (DECLARE) and Assignment (SET) are legal.

Allow conditional declarations.

Exception handling declarations are also allowed.

But here you have to remember that the function has a restricted condition: You cannot access the table in the function. Therefore, it is illegal to use the following statement in a 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

There are exactly the same restrictions in triggers.

Triggers are relatively new, So there will be (bugs) defects. So I'm here to warn you, as I said in the stored procedure book. Do not use this trigger in a database that contains important data, use it on some database for testing purposes if necessary, and confirm that the database is default when you create a trigger on a table.

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





Triggers must have a name, up to 64 characters, and may be followed by a separator. It is basically like the naming of other objects in MySQL.





Here I have a habit: to use the name of the table + '_' + the abbreviation of the trigger type. So if it is a table t26, the trigger is before the event update (refer to the point (2) and ( 3)) before (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
<Triggered SQL statement>

Triggers have time settings for execution: can be set to before or after the event occurs.