MySQL Basics: Triggers

Mysql triggers

1. Create a Trigger

　　1) Create a trigger that has only one execution statement:

CREATE  TRIGGER   Trigger name   | after   trigger event                   on    table name for each     ROW   Execution statement

The for every row indicates that the action on any one record satisfies the triggering event, triggers the trigger, and executes the final execution statement.

　　2) Create a trigger with multiple execution statements

CREATE  TRIGGER   Trigger name   | after   trigger event                   on    table name for each     ROW                      BEGIN                         execution Statement list                   END

2. View triggers

SHOW  TRIGGERS; SELECT *  from  Information_schema.triggers;

3. Using triggers

　　1) Insert Trigger　

Within the Insert trigger code, you can refer to a virtual table called NEW to access the inserted row;

In the Before insert trigger, the value in NEW can also be updated (allowing changes to the inserted value);

For the Auto_increment column, new contains 0 before the insert is executed, and the newly generated auto-generation value is included after the insert executes.

　　2) Update trigger

In the update trigger code, you can refer to a virtual table named old to access the value of the previous (before the UPDATE statement), referencing a virtual table named new to access the new updated value;

In the Before update trigger, the value in NEW may also be updated (allowing changes to be used for the value in the UPDATE statement);

The values in old are all read-only and cannot be updated.

　　3) Delete Trigger

Within the Delete trigger code, you can refer to a virtual table named old to access the deleted row;

The values in old are all read-only and cannot be updated.

4. Delete a trigger

DROP  TRIGGER   Trigger name;

MySQL Basics: triggers