Understand the basic concepts and uses of MySQL triggers

Understand the basic concepts and uses of MySQL triggers

MySQL trigger is a special type of stored procedure that defines a series of The action will be automatically activated and executed when the trigger condition is met. Through triggers, automated operations on database tables can be realized, such as executing corresponding logic when inserts, updates, deletes and other actions occur.

The basic syntax structure of MySQL trigger is as follows:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- Logic code executed by trigger
END;

In the above syntax structure, the name of the trigger is specified by trigger_name, and the execution time of the trigger is specified by BEFORE or AFTER Keywords are specified. The triggered action is specified by the INSERT, UPDATE or DELETE keywords. The table where the trigger is located is specified by table_name. FOR EACH ROW means that each row is triggered once, and the logic code between BEGIN and END is the logic code executed by the trigger.

The following uses several specific code examples to illustrate the usage of MySQL triggers:

1. Automatically update the data of another related table when inserting data

Assume there are two tables users and logs. When users insert new data into the users table, they need to insert new data into the logs table Automatically insert a relevant log record into the file. This can be achieved with the following trigger:

CREATE TRIGGER after_insert_user
AFTER INSERT
ON users
FOR EACH ROW
BEGIN
    INSERT INTO logs(user_id, action, timestamp)
    VALUES(NEW.id, 'Insert user', NOW());
END;

2. Record modification log when updating data

Suppose there is a products table. When the information of a certain product is updated, it needs to be recorded Below is the information before and after the modification. This can be achieved with the following trigger:

CREATE TRIGGER after_update_product
AFTER UPDATE
ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs(product_id, old_name, new_name, old_price, new_price, timestamp)
    VALUES(NEW.id, OLD.name, NEW.name, OLD.price, NEW.price, NOW());
END;

3. Conduct cascade operation when deleting data

Assume there are two tables orders and order_details, when When deleting an order, the corresponding order details need to be deleted in cascade. This can be achieved with the following trigger:

CREATE TRIGGER after_delete_order
AFTER DELETE
ON orders
FOR EACH ROW
BEGIN
    DELETE FROM order_details WHERE order_id = OLD.id;
END;

Through the above examples, you can see the role and use of MySQL triggers. Triggers can help us implement automated business logic during database operations and improve the reliability and efficiency of the database. When designing a database, rational use of triggers can simplify code logic, reduce repeated operations, and improve the maintainability and scalability of the system.

The above is the detailed content of Understand the basic concepts and uses of MySQL triggers. For more information, please follow other related articles on the PHP Chinese website!