What level is mysql trigger?

Mysql triggers are row-level. According to SQL standards, triggers can be divided into two types: 1. Row-level triggers, which will be activated once for each row of data modified. If a statement inserts 100 rows of data, the trigger will be called 100 times; 2. Statement-level triggers The trigger is activated once for each statement. A statement that inserts 100 rows of data will only call the trigger once. MySQL only supports row-level triggers, not prepared statement-level triggers.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Trigger overview

MySQL trigger is a stored procedure that is associated with a specified table. When the data in the table changes (Add, update, delete) automatically executed. These operations that modify data rows are called trigger events. For example, statements such as INSERT or LOAD DATA that insert data can activate insert triggers.

According to SQL standards, triggers can be divided into row-level triggers (row-level triggers) and statement-level triggers (statement-level triggers).

• The row-level trigger will be activated once for each row of data modified. If a statement inserts 100 rows of data, the trigger will be called 100 times;

• Statement-level triggers are activated once for each statement. A statement that inserts 100 rows of data will only call the trigger once.

• #MySQL only supports row-level triggers and does not support prepared statement-level triggers.

Different events can activate different types of triggers. The INSERT event trigger is used for inserting data operations, including INSERT, LOAD DATA, REPLACE statements, etc.; the UPDATE event trigger is used for update operations, such as UPDATE statements; the DELETE event trigger is used for delete operations, such as DELETE and REPLACE statements, etc., DROP TABLE and The TRUNCATE TABLE statement does not activate delete triggers.

In addition, MySQL triggers can be executed before or after the triggering event, which are called BEFORE triggers and AFTER triggers respectively. These two trigger timings can be combined with different trigger events, such as BEFORE INSERT trigger or AFTER UPDATE trigger.

The advantages of MySQL triggers include:

• Record and audit user modification operations on data in the table to achieve auditing functions;

• Implement integrity constraints that are more complex than check constraints, such as prohibiting data operations during non-business hours;

• Implement certain business logic, such as adding or Automatically update the number of people in the department when deleting employees;

• Synchronously replicate the data in the table in real time.

Although triggers are powerful, they also have some shortcomings:

• Triggers will increase the database structure Complexity, and triggers are invisible to the application and difficult to debug;

• Triggers need to occupy more database server resources. Try to use non-null, unique, and check constraints provided by the database. etc;

• Triggers cannot receive parameters and can only operate based on the current trigger object.

Using triggers for special scenarios can bring certain convenience; but do not rely too much on triggers to avoid database performance degradation and maintenance difficulties. Next we introduce the management operations of triggers.

Three triggers supported by MySQL

In actual use, MySQL supports three triggers: INSERT trigger, UPDATE triggers and DELETE triggers.

1) INSERT trigger

A trigger that responds before or after the INSERT statement is executed.

You need to pay attention to the following points when using INSERT triggers:

• In the INSERT trigger code, you can reference a virtual table named NEW (case-insensitive) to access the inserted row.

• In the BEFORE INSERT trigger, the value in NEW can also be updated, which allows the inserted value to be changed (as long as it has the corresponding operation permissions).

• For the AUTO_INCREMENT column, NEW contains the value 0 before the INSERT is executed and will contain the new automatically generated value after the INSERT is executed.

2) UPDATE trigger

A trigger that responds before or after the UPDATE statement is executed.

You need to pay attention to the following points when using UPDATE triggers:

• In the UPDATE trigger code, you can reference a virtual table named NEW (case-insensitive) to access the updated value.

• Within the UPDATE trigger code, a virtual table named OLD (case-insensitive) can be referenced to access the value before the UPDATE statement was executed.

• In the BEFORE UPDATE trigger, the value in NEW may also be updated, which allows changing the value to be used in the UPDATE statement (as long as you have the corresponding operation permissions).

  All values ​​in

• OLD are read-only and cannot be updated.

Note: When the trigger is designed to trigger the update operation of the table itself, only BEFORE type triggers can be used, and AFTER type triggers will not be allowed.

3) DELETE trigger

A trigger that responds before or after the DELETE statement is executed.

You need to pay attention to the following points when using DELETE triggers:

• In the DELETE trigger code, you can reference a virtual table named OLD (case-insensitive) to access deleted rows. All values ​​in

• OLD are read-only and cannot be updated.

Generally speaking, during the use of triggers, MySQL will handle errors in the following ways.

For transactional tables, if the trigger program fails, and the resulting entire statement fails, all changes performed by the statement will be rolled back; for non-transactional tables, such rollback cannot be performed , even if the statement fails, any changes made before the failure are still effective.

If the BEFORE trigger fails, MySQL will not perform the operation on the corresponding row.

If an error occurs during the execution of the BEFORE or AFTER trigger program, the entire statement calling the trigger program will fail.

MySQL will execute the AFTER trigger only if both the BEFORE trigger and the row operation have been successfully executed.

Create trigger

MySQL uses the CREATE TRIGGRT statement to create a trigger. The basic syntax is as follows:

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    trigger_body;

Among them, trigger_name is the name of the trigger; BEFORE and AFTER are used to specify the triggering time of the trigger; INSERT, UPDATE and DELETE are used to define the type of triggering event; table_name is the name of the table associated with the trigger, which cannot be a temporary table or view; FOR EACH ROW Indicates that this is a row-level trigger; trigger_body is the specific statement executed by the trigger.

For example, since employees’ salaries are important information, the salary modification history needs to be recorded. First, we create an audit table:

CREATE TABLE emp_salary_audit (
    audit_id    INTEGER NOT NULL AUTO_INCREMENT
    emp_id      INTEGER NOT NULL,
    old_salary  NUMERIC(8,2) NULL,
    new_salary  NUMERIC(8,2) NULL,
    change_date TIMESTAMP NOT NULL,
    change_by   VARCHAR(50) NOT NULL,
    CONSTRAINT pk_emp_salary_audit PRIMARY KEY (audit_id)
);

Among them, audit_id is the auto-increment primary key; emp_id is the employee number; old_salary and new_salary are used to store the monthly salary before and after modification respectively; change_date records the modification time; change_by The user who performed the modification operation is recorded.

Then create a trigger tri_audit_salary, which is used to record the modification record of employees’ monthly salary:

DELIMITER $$
CREATE TRIGGER tri_audit_salary
  AFTER UPDATE ON employee
  FOR EACH ROW
BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END$$
DELIMITER ;

Among them, DELIMITER is used to modify the end character of the SQL statement, which we already have when introducing the stored procedure. As you know; AFTER means executing the trigger after modifying the data; UPDATE means recording data changes only for update operations; NEW and OLD in the trigger body are special variables in MySQL triggers, including records after modification and before modification. There is no OLD variable for the INSERT trigger, and there is no NEW variable for the DELETE trigger; CURRENT_TIMESTAMP and USER() are MySQL system functions that return the current time and logged-in user.

After creating the trigger, we perform some data modification operations to verify the effect of the trigger:

UPDATE employee
SET email = 'sunqian@shuguo.net'
WHERE emp_name = '孙乾';

UPDATE employee
SET salary = salary * 1.1
WHERE emp_name = '孙乾';

SELECT *
FROM salary_audit;
audit_id|emp_id|old_salary|new_salary|change_date        |change_by|
--------|------|----------|----------|-------------------|---------|
       1|    25|      4700|      5170|2019-10-18 10:16:36|TONY     |

The first UPDATE statement only modified the email address of "Sun Qian", so Tri_audit_salary will not be triggered; the second UPDATE statement modifies his monthly salary, triggering tri_audit_salary. Therefore, the audit table salary_audit contains a piece of data that records the situation before and after the monthly salary change.

If you want to audit the operations of adding employees and deleting employees at the same time, you can create an INSERT trigger and a DELETE trigger.

In addition, MySQL supports defining multiple triggers for the same triggering timing and the same event, and specifying their execution order:

CREATE TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON table_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger
    trigger_body;

Among them, FOLLOWS indicates that the trigger is in The trigger is executed after other_trigger; PRECEDES indicates that the trigger is executed before other_trigger; if no options are specified, by default, the triggers are executed in the order in which they are created.

View triggers

Use the SHOW TRIGGERS statement to view the list of triggers in the database:

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Among them, db_name Used to view triggers in the specified database, the default is the current database; LIKE is used to match the name of the stored procedure, and WHERE can specify more filter conditions. For example, the following statement returns the triggers in the current database:

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tri_audit_salary
               Event: UPDATE
               Table: employee
           Statement: BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
              Timing: AFTER
             Created: 2020-10-06 21:50:02.47
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

In addition, the MySQL system table INFORMATION_SCHEMA.TRIGGERS contains more detailed trigger information.

If you want to get the DDL statement that creates a trigger, you can SHOW CREATE TRIGGER statement. For example:

mysql> SHOW CREATE TRIGGER tri_audit_salary\G
*************************** 1. row ***************************
               Trigger: tri_audit_salary
              sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tri_audit_salary` AFTER UPDATE ON `employee` FOR EACH ROW BEGIN
  -- 当月薪改变时，记录审计数据
  IF (NEW.salary <> OLD.salary) THEN
   INSERT INTO salary_audit (emp_id, old_salary, new_salary, change_date, change_by)
   VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP, USER());
  END IF;
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-10-06 21:50:02.47
1 row in set (0.00 sec)

Delete trigger

MySQL does not provide a statement to modify a trigger. You can only delete and create a trigger again through the DROP TRIGGER statement. device. For example, the following statement can be used to delete the trigger tri_audit_salary:

DROP TRIGGER IF EXISTS tri_audit_salary;

IF EXISTS avoids an error if the trigger tri_audit_salary does not exist.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What level is mysql trigger?. For more information, please follow other related articles on the PHP Chinese website!