SQL语法——触发器

最新推荐文章于 2025-10-30 15:41:31 发布

原创最新推荐文章于 2025-10-30 15:41:31 发布 · 1.2w 阅读

31 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL 专栏收录该内容

33 篇文章

订阅专栏

MySQL触发器简介

在MySQL中，触发器是一组SQL语句，当对关联表上的数据进行更改时会自动调用这些语句。可以定义触发器在INSERT，UPDATE或DELETE语句更改数据之前或之后调用。在MySQL 5.7.2版之前，可以为每个表定义最多六个触发器。

BEFORE INSERT - 在将数据插入表格之前激活。
AFTER INSERT - 将数据插入表格后激活。
BEFORE UPDATE - 在更新表中的数据之前激活。
AFTER UPDATE - 更新表中的数据后激活。
BEFORE DELETE - 在从表中删除数据之前激活。
AFTER DELETE - 从表中删除数据后激活。

但是，从MySQL版本5.7.2+开始，可以为同一触发事件和操作时间定义多个触发器。

当使用不包含INSERT，DELETE或UPDATE语句来修改表中的数据，与表关联的触发器不被调用。例如，TRUNCATE语句删除表的所有数据，但不调用与表关联的触发器。

有一些语句使用INSERT其它语句，如REPLACE语句或 LOAD DATA语句。如果使用这些语句，则会调用与表关联的相应触发器。

必须为与表关联的每个触发器使用唯一名称。但是可以为不同的表定义相同的触发器名称。

命名规则：

(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE) 

-- 或

tablename_(BEFORE | AFTER)_(INSERT| UPDATE | DELETE)

MySQL触发存储

MySQL将触发器存储在数据目录中，例如，/data/mysqldemo/使用名为的文件tablename.TRG和 triggername.TRN：

tablename.TRG文件将触发器映射到相应的表。
triggername.TRN文件包含触发器定义。

MySQL触发器限制

MySQL触发器不能：

使用SHOW，LOAD DATA，LOAD TABLE，BACKUP DATABASE，RESTORE，FLUSH和RETURN语句。
使用隐式或显式提交或回滚的语句，例如COMMIT，ROLLBACK，START TRANSACTION，LOCK / UNLOCK TABLES，ALTER，CREATE，DROP， RENAME。
使用准备语句 PREPARE和EXECUTE之类的
使用动态SQL语句。

从MySQL版本5.1.4开始，触发器可以调用存储过程或存储函数，这是以前版本的限制

MySQL触发语法

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END;

在CREATE TRIGGER语句后面添加了触发器名称。触发器名称应遵循的命名约定[trigger time][table name][trigger event]，例如before_employees_update。
触发激活时间可以是BEFORE或AFTER。您必须在定义触发器时指定激活时间。使用BEFORE对表进行更改之前处理操作，然而需要在更改后处理操作，则使用 AFTER关键字。
触发事件可以是INSERT，UPDATE或者DELETE事件导致触发器被调用。只有一个事件可以调用触发器。要定义由多个事件调用的触发器，您必须定义多个触发器，每个事件对应一个触发器。
触发器必须与特定表关联。如果没有表触发器将不存在，因此您必须在ON关键字后指定表名。
将SQL语句放在BEGIN和END阻止之间。您可以在此定义触发器的逻辑。

MySQL触发器示例

首先，创建一个表名为 employees_audit保持更改employee表。以下语句创建employee_audit表。

CREATE TABLE employees_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employeeNumber INT NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

接下来，创建BEFORE UPDATE在对employees表进行更改之前调用的触发器。

DELIMITER $$
CREATE TRIGGER before_employee_update 
	BEFORE UPDATE ON employees 
	FOR EACH ROW
BEGIN
	INSERT INTO employees_audit 
	SET action = 'update',
	employeeNumber = OLD.employeeNumber,
	lastname = OLD.lastname,
	changedat = NOW( );
END $$
DELIMITER ;

然后，要查看当前数据库中的所有触发器，请使用SHOW TRIGGERS如下语句：

SHOW TRIGGERS;

之后，更新employees表以检查是否调用了触发器。

UPDATE employees 
SET 
    lastName = 'Phan'
WHERE
    employeeNumber = 1056;

最后，要检查UPDATE语句是否调用了触发器，可以employees_audit使用以下查询查询表：

SELECT 
    *
FROM
    employees_audit;

在这里插入图片描述

MySQL 创建多个触发器

在MySQL 5.7.2版之前，只能为表中的事件创建一个触发器，例如，您只能为BEFORE UPDATE或AFTER UPDATE事件创建一个触发器。MySQL 5.7.2+解除了这一限制，允许您为表中的相同事件和操作时间创建多个触发器。事件发生时，触发器将按顺序激活。

创建第一个触发器的语法保持不变。如果在表中有相同事件的多个触发器，MySQL将按创建顺序调用触发器。要更改触发器的顺序，需要指定FOLLOWS或PRECEDES在FOR EACH ROW子句之后。

FOLLOWS 选项允许在现有触发器之后激活新触发器。
PRECEDES 选项允许在现有触发器之前激活新触发器。

以下是使用显式顺序创建新的附加触发器的语法：

DELIMITER $$
CREATE TRIGGER  trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
FOR EACH ROW [FOLLOWS|PRECEDES] existing_trigger_name
BEGIN
…
END$$
DELIMITER ;