摘要:在本教程中,您将学习如何创建 MySQL AFTER DELETE
触发器来维护另一个表的汇总表。
MySQL AFTER DELETE
触发器简介
MySQL AFTER DELETE
触发器在表上发生删除事件后自动调用。
以下是创建 MySQL AFTER DELETE
触发器的基本语法:
CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
trigger_body;
Code language: SQL (Structured Query Language) (sql)
在这个语法中:
首先,在CREATE TRIGGER
子句中指定要创建的触发器的名称。
其次,使用AFTER DELETE
子句指定调用触发器的时间。
第三,在ON
关键字后指定与触发器关联的表的名称。
最后,指定触发器主体,其中包含调用触发器时执行的一个或多个语句。
如果您在trigger_body
中有多个语句,则需要使用BEGIN END
块来包装它们并翻转$$
和;
之间的默认分隔符。如下图所示:
DELIMITER $$
CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
在AFTER DELETE
触发器中,您可以访问OLD
行,但无法更改它。
请注意, AFTER DELETE
触发器中没有NEW
行。
MySQL AFTER DELETE
触发器示例
请考虑以下AFTER DELETE
触发器示例。
设置样本表
首先,创建一个名为Salaries
的新表:
DROP TABLE IF EXISTS Salaries;
CREATE TABLE Salaries (
employeeNumber INT PRIMARY KEY,
salary DECIMAL(10,2) NOT NULL DEFAULT 0
);
Code language: SQL (Structured Query Language) (sql)
其次,在Salaries
表中插入一些行:
INSERT INTO Salaries(employeeNumber,salary)
VALUES
(1002,5000),
(1056,7000),
(1076,8000);
Code language: SQL (Structured Query Language) (sql)
第三,创建另一个名为SalaryBudgets
表,用于存储Salaries
表中的工资总额:
DROP TABLE IF EXISTS SalaryBudgets;
CREATE TABLE SalaryBudgets(
total DECIMAL(15,2) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
第四,使用SUM()
函数从Salaries
表中获取总工资并将其插入到SalaryBudgets
表中:
INSERT INTO SalaryBudgets(total)
SELECT SUM(salary)
FROM Salaries;
Code language: SQL (Structured Query Language) (sql)
最后,从SalaryBudgets
表中查询数据:
SELECT * FROM SalaryBudgets;
Code language: SQL (Structured Query Language) (sql)
创建AFTER DELETE
触发器示例
从Salaries
表中删除行后,以下AFTER DELETE
触发器会更新SalaryBudgets
表中的总工资:
CREATE TRIGGER after_salaries_delete
AFTER DELETE
ON Salaries FOR EACH ROW
UPDATE SalaryBudgets
SET total = total - old.salary;
Code language: SQL (Structured Query Language) (sql)
在此触发器中:
首先,触发器的名称是CREATE TRIGGER
子句中指定的after_salaries_delete
:
CREATE TRIGGER after_salaries_delete
Code language: SQL (Structured Query Language) (sql)
其次,触发事件是:
AFTER DELETE
Code language: SQL (Structured Query Language) (sql)
第三,触发器关联的表是Salaries
表:
ON Salaries FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)
最后,在触发器体内,我们从总工资中减去删除的工资。
测试 MySQL AFTER DELETE
触发器
首先,从 Salaries 表中删除一行:
DELETE FROM Salaries
WHERE employeeNumber = 1002;
Code language: SQL (Structured Query Language) (sql)
其次,从SalaryBudgets表中查询工资总额:
SELECT * FROM SalaryBudgets;
Code language: SQL (Structured Query Language) (sql)
从输出中可以看到,总计减少了删除的工资。
第三,从工资表中删除所有行:
DELETE FROM Salaries;
Code language: SQL (Structured Query Language) (sql)
最后,从 SalaryBudgets 表中查询总计:
SELECT * FROM SalaryBudgets;
Code language: SQL (Structured Query Language) (sql)
触发器将总数更新为零。
在本教程中,您学习了如何创建 MySQL AFTER DELETE
触发器来维护另一个表的汇总表。