Detailed explanation of the definition and use of MySQL triggers

Detailed explanation of the definition and use of MySQL triggers

MySQL triggers are a special stored procedure that can be automatically executed when a specific event occurs in the table. Triggers can be used to implement functions such as automated data processing and data consistency maintenance. This article will introduce the definition and use of MySQL triggers in detail, and provide specific code examples.

1. Definition of triggers
   In MySQL, the definition of triggers is achieved through the CREATE TRIGGER statement. The CREATE TRIGGER syntax is as follows:

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name FOR EACH ROW
BEGIN
    --SQL statement executed by trigger
END;

Among them, CREATE TRIGGER is used to define the trigger, trigger_name is the name of the trigger, BEFORE/AFTER indicates whether the trigger is executed before or after the event, and INSERT/UPDATE/DELETE indicates whether the trigger is for For insert, update or delete operations, table_name is the name of the table to which the trigger belongs. FOR EACH ROW means that the trigger is executed once for each row of data. Between BEGIN and END is the SQL statement specifically executed by the trigger.

2. How to use triggers
   Next, we use a specific example to demonstrate the use of MySQL triggers. Suppose we have a student table (student), which contains the student's name and score fields. We want to automatically calculate the student's grade (level) when inserting new student data.

First, we create a trigger named student_level, the code is as follows:

DELIMITER $$

CREATE TRIGGER student_level_trigger
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
    DECLARE level VARCHAR(10);
    IF NEW.score >= 90 THEN
        SET level = 'A';
    ELSEIF NEW.score >= 80 THEN
        SET level = 'B';
    ELSEIF NEW.score >= 70 THEN
        SET level = 'C';
    ELSE
        SET level = 'D';
    END IF;
    
    SET NEW.level = level;
END;
$$

DELIMITER ;

In the above code, we created a BEFORE INSERT type trigger student_level_trigger. When new student data is inserted, the trigger will automatically set the student's level based on the student's performance and update it. into the level field.

Next, we insert a piece of student data to test whether the trigger is effective:

INSERT INTO student(name, score) VALUES('Xiao Ming', 85);

Execute the above After inserting the statement, query the data in the student table. You can see that the student's level has been calculated and filled in the level field.

Summary: This article introduces the definition and use of MySQL triggers in detail, and demonstrates the practical application of triggers through specific examples. Triggers are a very powerful function in MySQL, which can realize functions such as automation of data processing and maintenance of data consistency. Developers can flexibly use triggers according to actual needs to improve the efficiency and accuracy of data processing.

The above is the detailed content of Detailed explanation of the definition and use of MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!