Mysql 简明教程
MySQL - Before Delete Trigger
在 MySQL 中，触发器被定义为一个特殊的存储过程，它驻留在系统目录中，并在执行事件时执行。它被称为特殊存储过程，因为它不需要像其他存储过程那样显式调用。每当所需的事件被触发时，触发器都会自动执行。触发器分为两类 -
In MySQL, a trigger is defined a special stored procedure that resides in the system catalogue, and is executed whenever an event is performed. It 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
-
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 Before Delete Trigger
Before Delete Trigger 是 MySQL 数据库支持的行级触发器。Before Delete Trigger 在从数据库表的一行中删除值之前立即执行。
The Before Delete Trigger is a row-level trigger supported by the MySQL database. The Before Delete Trigger is executed right before a value is deleted from a row of a database table.
有了这个触发器，每当在数据库中执行 DELETE 语句时，值会被首先从表中删除，然后执行触发器集。
With this trigger, whenever a DELETE statement is executed in the database, the value is deleted from a table first followed by execution of the trigger set.
Syntax
以下是 MySQL 中创建 BEFORE DELETE 触发器的语法 -
Following is the syntax to create the BEFORE DELETE trigger in MySQL −
CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name FOR EACH ROW
BEGIN
-- trigger body
END;Example
在此示例中，我们使用以下查询创建一个名为 'CUSTOMERS' 的表，以演示 BEFORE DELETE 触发器 -
In this example, we are creating a table named 'CUSTOMERS', to demonstrate the BEFORE 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)
);使用以下 CREATE TRIGGER 语句在 CUSTOMERS 表上创建一个新的触发器 'before_delete_trigger'，以从 CUSTOMERS 表中删除客户详细信息并将其插入到另一个表 'OLD_CUSTOMERS' 中 -
Using the following CREATE TRIGGER statement, create a new trigger 'before_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 before_delete_trigger
BEFORE 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
为了验证是否已从 OCUSTOMERS 表中删除详细信息并添加到 OLD_CUSTOMERS 表中，让我们尝试使用 SELECT 查询检索这两个结果集。
To verify whether the details are deleted from the OCUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using 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 表中。
As you can in the tables above, the data has been deleted from the CUSTOMERS table and added to the OLD_CUSTOMERS table.