摘要:在本教程中,您将学习如何创建 MySQL BEFORE DELETE
触发器以将已删除的行添加到存档表中。
MySQL BEFORE DELETE
触发器简介
MySQL BEFORE DELETE
触发器在表中发生删除事件之前自动触发。
以下是创建 MySQL BEFORE DELETE
触发器的基本语法:
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
trigger_body
Code language: SQL (Structured Query Language) (sql)
在这个语法中:
首先,在CREATE TRIGGER
关键字后指定要创建的触发器的名称。
其次,使用BEFORE DELETE
子句指定在删除事件之前调用触发器。
第三,在ON
关键字后指定触发器关联的表的名称。
最后,指定触发器主体,该主体由触发触发器时执行的一个或多个语句组成。
请注意,如果您在trigger_body
中有多个语句,则需要使用BEGIN END
块来包装这些语句并暂时更改默认分隔符,如下所示:
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
在BEFORE DELETE
触发器中,您可以访问OLD
行,但无法更新它。此外, BEFORE DELETE
触发器中没有NEW
行。
MySQL BEFORE DELETE
触发器示例
让我们看看下面的BEFORE DELETE
触发器示例。
设置样本表
首先,新建一个表,名为Salaries
,存储员工的工资信息
DROP TABLE IF EXISTS Salaries;
CREATE TABLE Salaries (
employeeNumber INT PRIMARY KEY,
validFrom DATE NOT NULL,
amount DEC(12 , 2 ) NOT NULL DEFAULT 0
);
Code language: SQL (Structured Query Language) (sql)
其次,在Salaries
表中插入一些行:
INSERT INTO salaries(employeeNumber,validFrom,amount)
VALUES
(1002,'2000-01-01',50000),
(1056,'2000-01-01',60000),
(1076,'2000-01-01',70000);
Code language: SQL (Structured Query Language) (sql)
三、创建一个表,存储删除的工资:
DROP TABLE IF EXISTS SalaryArchives;
CREATE TABLE SalaryArchives (
id INT PRIMARY KEY AUTO_INCREMENT,
employeeNumber INT PRIMARY KEY,
validFrom DATE NOT NULL,
amount DEC(12 , 2 ) NOT NULL DEFAULT 0,
deletedAt TIMESTAMP DEFAULT NOW()
);
Code language: SQL (Structured Query Language) (sql)
创建BEFORE DELETE
触发器示例
以下BEFORE DELETE
触发器在删除Salaries
表中的行之前将新行插入到SalaryArchives
表中。
DELIMITER $$
CREATE TRIGGER before_salaries_delete
BEFORE DELETE
ON salaries FOR EACH ROW
BEGIN
INSERT INTO SalaryArchives(employeeNumber,validFrom,amount)
VALUES(OLD.employeeNumber,OLD.validFrom,OLD.amount);
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
在此触发器中:
首先,触发器的名称是CREATE TRIGGER
子句中指定的before_salaries_delete
:
CREATE TRIGGER before_salaries_delete
Code language: SQL (Structured Query Language) (sql)
其次,触发事件是:
BEFORE DELETE
Code language: SQL (Structured Query Language) (sql)
第三,触发器关联的表是Salaries
表:
ON Salaries FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)
最后,在触发器主体内将已删除的行插入SalaryArchives
表中。
测试 MySQL BEFORE DELETE
触发器
首先,从Salaries
表中删除一行:
DELETE FROM salaries
WHERE employeeNumber = 1002;
Code language: SQL (Structured Query Language) (sql)
其次,从SalaryArchives
表中查询数据:
SELECT * FROM SalaryArchives;
Code language: SQL (Structured Query Language) (sql)
调用触发器并将新行插入到SalaryArchives
表中。
第三,删除 Salaries 表中的所有行:
DELETE FROM salaries;
Code language: SQL (Structured Query Language) (sql)
最后,从SalaryArchives
表中查询数据:
SELECT * FROM SalaryArchives;
Code language: SQL (Structured Query Language) (sql)
该触发器被触发两次,因为DELETE
语句从Salaries
表中删除了两行。
在本教程中,您学习了如何创建 MySQL BEFORE DELETE
触发器以将已删除的行添加到存档表中。