Detailed explanation of MySQL delete trigger (delete) usage

DELETE trigger

Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by a program or started manually, but is triggered by events. For example, it is activated when a table is operated (insert, delete, update). It executes. Triggers are often used to enforce data integrity constraints and business rules. Triggers can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionaries. A SQL3 trigger is a statement that can be automatically executed by the system to modify the database.

Recommended related mysql video tutorials: "mysql tutorial"

Triggers can query other tables and can contain complex SQL statements. They are primarily used to enforce compliance with complex business rules or requirements. For example: You can control whether new orders are allowed to be inserted based on the customer's current account status.

DELETE trigger executes before or after the DELETE statement is executed. You need to know the following two points:

1. Within the DELETE trigger code, you can reference a virtual table named OLD to access the deleted rows;

2. All values ​​in OLD are read-only and cannot be updated. The following example demonstrates using OLD to save rows to be deleted into an archive table:

Input:

create trigger deleteorder before delete on orders for each row
begin
insert into archive_orders(order_num,order_date,cust_id)
values(old.order_num,old.order_date,old.cust_id);
end;

Analysis: This trigger will be executed before any order is deleted. It uses an INSERT statement to save the values ​​in OLD (the orders to be deleted) into an archive table called archive_orders (to actually use this example, you need to create a table called archive_orders with the same columns as orders) .

The advantage of using the BEFORE DELETE trigger (compared to the AFTER DELETE trigger) is that if for some reason the order cannot be archived, the DELETE itself will be abandoned.

Multi-statement trigger As you can see, the trigger deleteorder uses BEGIN and END statements to mark the trigger body. This isn't necessary in this example, but it doesn't hurt either. The advantage of using the BEGIN END block is that the trigger can accommodate multiple SQL statements (one next to one in the BEGIN END block).

【Related recommendations】

1. mysql free video tutorial

2. Detailed explanation of insert trigger (insert) in MySQL

3. Introduction to mysql triggers and how to create and delete triggers

4. Tutorial on using character sets and collation order for MySQL

5. Introduction to MySQL character set and collation order

The above is the detailed content of Detailed explanation of MySQL delete trigger (delete) usage. For more information, please follow other related articles on the PHP Chinese website!