How to use parameters in MySQL triggers

MySQL trigger is a special program used in the database management system to monitor the operations of a specific table and perform corresponding operations based on predefined conditions. When creating a MySQL trigger, we can use parameters to flexibly pass data and information, making the trigger more versatile and applicable.

In MySQL, triggers can trigger and execute corresponding logic before or after the INSERT, UPDATE, and DELETE operations of a specific table. Using parameters can make triggers more flexible, and you can pass the required data to the trigger as needed.

Let's look at some specific code examples to demonstrate the use of parameters in MySQL triggers.

First, we create a sample table students to store student information:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    ageINT
);

Next, we create a sample trigger trig_student_audit, which will automatically record the operation information every time an INSERT operation is performed on the students table:

DELIMITER $$
CREATE TRIGGER trig_student_audit BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, user, timestamp)
    VALUES ('students', 'INSERT', USER(), NOW());
END$$
DELIMITER ;

In the above code example, we created a trigger named trig_student_audit, which fires before each new data is inserted into the students table , an operation record is inserted into the audit_log table inside the trigger, recording the table name, operation type, user who performed the operation, and timestamp.

Next, let’s demonstrate an example of a trigger with parameters. Suppose we want to create a trigger that automatically inserts new student information into another table student_backup after inserting new data:

DELIMITER $$
CREATE TRIGGER trig_student_backup AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_backup (id, name, age, created_at)
    VALUES (NEW.id, NEW.name, NEW.age, NOW());
END$$
DELIMITER ;

In this example, we created a trigger named trig_student_backup, which fires after inserting data and inserts new students into the student_backup table information, in which the newly inserted data row is accessed through the parameter NEW.

Through the above trigger examples, we show how to use parameters in MySQL triggers. Triggers can not only execute specific logic before and after database operations, but can also pass data and information through parameters, making trigger logic more flexible and powerful. Developers can flexibly use triggers and parameters based on specific needs and business scenarios to achieve more intelligent and efficient database management and operations.

The above is the detailed content of How to use parameters in MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!