Mysql 简明教程
MySQL - After Delete Trigger
一般来说， Trigger 被定义为对事件的响应。在 MySQL 中，触发器被称为特殊存储过程，因为它不需要像其他存储过程那样显式调用。触发器会在所需事件触发时自动执行。触发器分为两类：Before Triggers 和 After Triggers。
In general, a Trigger is defined as a response to an event. In MySQL, a trigger is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. Triggers are categorized into two types: Before Triggers and After Triggers.
这些触发器可以响应表上的插入操作、更新操作或删除操作。因此，每当执行 INSERT、UPDATE 或 DELETE 语句时，这些特殊存储过程就会响应。
These triggers can be a response to either insertion operation on a table, update operation or deletion operation. Thus, these special stored procedures respond whenever INSERT, UPDATE or DELETE statements are executed.
MySQL After Delete Trigger
After Delete 触发器是 MySQL 数据库支持的行级触发器。此触发器在从数据库表行中删除值后立即执行。
The After Delete Trigger is a row-level trigger supported by the MySQL database. This trigger is executed right after a value is deleted from a row of a database table.
当在数据库中执行 DELETE 语句时，会首先执行触发器，然后从表中删除该值。
When a DELETE statement is executed in the database, the trigger is performed first and then the said value is deleted from the table.
Syntax
以下是创建 MySQL 中 AFTER DELETE 触发器语法 -
Following is the syntax to create the AFTER DELETE trigger in MySQL −
CREATE TRIGGER trigger_name
AFTER DELETE ON table_name FOR EACH ROW
BEGIN
-- trigger body
END;Example
在此示例中，我们创建一个名为“CUSTOMERS”的表，使用以下查询来演示 AFTER DELETE 触发器 -
In this example, we are creating a table named 'CUSTOMERS', to demonstrate the AFTER DELETE trigger on, using the following query −
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);使用以下 INSERT 语句向创建的表中插入值 -
Insert values into this table created using the following INSERT statements −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );Creating Another Table:
Creating Another Table:
现在，让我们创建一个另一个空表，以存储从主表 'CUSTOMERS' 中删除的所有以前客户 -
Now, let us create another empty table to store all former customers after being deleted from the main table 'CUSTOMERS' −
CREATE TABLE OLD_CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(18, 2),
PRIMARY KEY(ID)
);使用以下创建触发器语句，在 CUSTOMERS 表上创建一个新触发器“after_delete_trigger”，从 CUSTOMERS 表删除客户详细信息并将其插入另一个表“OLD_CUSTOMERS”中 -
Using the following CREATE TRIGGER statement, create a new trigger 'after_delete_trigger' on the CUSTOMERS table to delete the customer details from CUSTOMERS table and insert them into another table "OLD_CUSTOMERS" −
DELIMITER //
CREATE TRIGGER after_delete_trigger
AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES
(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //
DELIMITER ;使用如下所示的常规 DELETE 语句从 CUSTOMERS 表中删除 'old' 客户的详细信息 -
Delete details of 'old' customers from the CUSTOMERS table using the regular DELETE statement as shown below −
DELETE FROM CUSTOMERS WHERE ID = 3;Verification
要验证详细信息是否从 CUSTOMERS 表中删除并添加到 OLD_CUSTOMERS 表中，让我们尝试使用 SELECT 查询检索这两个 result 集。
To verify whether the details are deleted from the CUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using the SELECT queries.
CUSTOMERS 表中的记录如下 -
The records in CUSTOMERS table are as follows −
OLD_CUSTOMERS 表中的记录如下 -
The records in OLD_CUSTOMERS table are as follows −
如上表所示，数据已从 CUSTOMERS 表中删除并添加到 OLD_CUSTOMERS 表中。然而，唯一看似不可见的是，触发器是在删除完成后执行的，与 BEFORE DELETE 触发器相反。
As you can in the tables above, the data has been deleted from the CUSTOMERS table and added to the OLD_CUSTOMERS table. However, the only difference that is not visible on the application level is that the trigger is performed after the deletion is done, in contrast to the BEFORE DELETE trigger.