Use of MySQL trigger

1. Trigger Introduction

1. What is a trigger?

A trigger is a database object associated with a table that is triggered when the defined conditions are met and executes the set of statements defined in the trigger.

2. Characteristics of triggers

• There is a body called begin begin end ; the statements between begin end end can be simple or complex.
• What conditions trigger: I, D, U
• When to trigger: before or after adding, deleting, or modifying
• Trigger frequency: Execute for each row
• Triggers are defined on tables and attached to them.

That is, an operation is triggered by an event, which includes INSERT statements, UPDATE statements, and DELETE statements; it can help the application ensure data integrity on the database side.

Note: cannot associate a trigger with a TEMPORARY table or a view.

! ! Use triggers as little as possible and it is not recommended.

Assuming that the trigger takes 1 second each time it is triggered insert table 500 , then the trigger needs to be triggered 500 times. The trigger execution time alone takes 500s , and insert 500 records takes a total of 1 second, so the efficiency of this insert is very low. Therefore, one thing we need to pay special attention to is that the execution efficiency of the statements between的begin end the trigger must be high and the resource consumption must be small.

Use triggers as little as possible, because no matter what, they are still very resource-consuming. If used, use them with caution and make sure they are very efficient: triggers are for each row; remember not to use triggers on tables that are frequently added, deleted, and modified, because they are very resource-consuming.

2. Create a trigger

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
  [trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

> BEFORE and AFTER parameters specify when the trigger is executed, before or after the event.

> FOR EACH ROW means that any operation on a record that satisfies the trigger event will trigger the trigger, that is, the trigger is triggered once for each row of data.

> tigger_event details:

①INSERT型觸發器: The trigger is activated when a row is inserted, which may be triggered by INSERT , LOAD DATA , or REPLACE statements (the LOAD DAT statement is used to load a file into a data table, which is equivalent to a series of INSERT operations);

②UPDATE型觸發器: The trigger is activated when a row is changed, which may be triggered by UPDATE statement;

③DELETE型觸發器: The trigger is activated when a row is deleted, which may be triggered by DELETE or REPLACE statements.

> trigger_order is a feature after MySQL5.7 , which is used to define multiple triggers and use follows (trailing) or precedes (before) to select the order in which the triggers are executed.

1. Create a trigger with only one execution statement

CREATE TRIGGER trigger name BEFORE|AFTER trigger event ON table name FOR EACH ROW execute statement;

Example 1: Create a trigger named trig1. Once an insert action is performed in the work table, the current time will be automatically inserted into the time table.

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());
 

2. Create a trigger with multiple execution statements

CREATE TRIGGER trigger name BEFORE|AFTER trigger event

ON table name FOR EACH ROW

BEGIN

Execute statement list

END;

Example 2: Define a trigger that executes the statements in BEGIN and END once a delete operation meets the conditions.

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

3. Detailed explanation of NEW and OLD

MySQL defines NEW and OLD to indicate the row of data in the table where the trigger is located, and to reference the record content that has changed in the trigger. Specifically:

①在INSERT , NEW is used to indicate new data that is about to be inserted ( BEFORE ) or has been inserted ( AFTER );

②In an在UPDATE trigger , OLD is used to indicate the original data that is about to be or has been modified, and NEW is used to indicate the new data that is about to be or has been modified;

③In a在DELETE trigger , OLD is used to indicate the original data that is about to be or has been deleted;

Directions:

NEW.columnName (columnName is the name of a column in the corresponding data table)

In addition, OLD is read-only, while NEW can use SET assignment in the trigger, so that the trigger will not be triggered again, resulting in a circular call (for example, before inserting each student, add "2013" to the student number).

Example 3:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.amount < 0 THEN
    -> SET NEW.amount = 0;
    -> ELSEIF NEW.amount > 100 THEN
    -> SET NEW.amount = 100;
    -> END IF;
    -> END$$
mysql> delimiter;

mysql> update account set amount=-10 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 0.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+

mysql> update account set amount=200 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 100.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
 

3. View triggers

1. SHOW TRIGGERS statement to view trigger information

mysql> SHOW TRIGGERS\G;

…

As a result, basic information of all triggers is displayed; the specified trigger cannot be queried.

2. View trigger information in the information_schema.triggers table

mysql> SELECT * FROM information_schema.triggers\G

…

As a result, detailed information of all triggers is displayed; at the same time, this method can query detailed information of a specified trigger.

mysql> select * from information_schema.triggers 
    -> where trigger_name='upd_check'\G;
Tips:

All trigger information is stored in the triggers table under the information_schema database and can be queried using the SELECT statement. If there is too much trigger information, it is best to specify the query using the TRIGGER_NAME field.

4. Delete trigger

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

After deleting the trigger, it is best to check it again using the above method; at the same time, you can also use database.trig to specify a trigger in a certain database.

Tips:

It is critical to delete a trigger if it is no longer needed to avoid unexpected operations.

This is the end of this article about the use of MySQL trigger . For more information about the use of MySQL trigger , please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!