Application of trigger for MySQL notes

Create a trigger

Create a trigger with only one statement to execute

CREATE TRIGGER  The trigger name  BEFORE|AFTER  Triggering event 
ON  The name of the table  FOR EACH ROW  Execute the statement 

Where the trigger name parameter refers to the name of the trigger to be created

The BEFORE and AFTER parameters specify when the execution will be triggered, before or after the event

FOR EACH ROW means that any action on a record that satisfies a trigger event will trigger the trigger

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());
Query OK, 0 rows affected (0.09 sec)

A trigger called trig1 was created above, and once an action is inserted in work, the current time is automatically inserted into the time table

Create a trigger with multiple execution statements

CREATE TRIGGER  The trigger name  BEFORE|AFTER  Triggering event 
ON  The name of the table  FOR EACH ROW
BEGIN
     Execute statement list 
END

Among them, the execution statement list parameter between BEGIN and END represents multiple statements that need to be executed, and the different statements are separated by semicolons

tips: 1 normally, mysql defaults to; As an end execution statement, it conflicts with the desired branch in the trigger

To solve this problem, DELIMITER can be used, such as: DELIMITER ||, and the ending symbol can be changed to ||

When the trigger is created, you can use DELIMITER; To turn the closing symbol into;

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> INSERT INTO time VALUES(NOW());
    -> INSERT INTO time VALUES(NOW());
    -> END
    -> ||
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER ;

In the above statement, the ending symbol is defined as || at the beginning, and a trigger is defined in the middle, with 1 delete operation that satisfies the condition

The statements in BEGIN and END are executed, and then ended with |, |

Finally, DELIMITER is used. Restores the closing symbol

View trigger
The SHOW TRIGGERS statement views the trigger information

mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
             Trigger: trig1
               Event: INSERT
               Table: work
           Statement: INSERT INTO time VALUES(NOW())
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

The result shows the basic information for all triggers

tips: the SHOW TRIGGERS statement could not query the specified trigger

View the trigger information in the triggers table

mysql> SELECT * FROM information_schema.triggers\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: person
              TRIGGER_NAME: trig1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: person
        EVENT_OBJECT_TABLE: work
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: INSERT INTO time VALUES(NOW())

The result shows the details of all triggers, and the method queries the details of formulating triggers

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='trig1'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: person
              TRIGGER_NAME: trig1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: person
        EVENT_OBJECT_TABLE: work

tips: all trigger information is stored in the triggers table under the information_schema database

You can query using SELECT statements, and if there is too much trigger information, it is best to specify the query through the TRIGGER_NAME field

Delete trigger

mysql> DROP TRIGGER trig1;
Query OK, 0 rows affected (0.04 sec)

It is best to view the trigger 1 time after removing it using the above method

You can also use database.trig to specify triggers in a database

tips: if a trigger is not needed, it must be removed to avoid unexpected operations