SQL触发器：类型、应用、工作原理等

SQL 触发器就像数据库中的自动化例程，当表中发生特定事件（例如 INSERT、UPDATE 或 DELETE）时，它会执行预定义的操作。这有助于自动执行数据更新并设置一些规则。它可以保持数据的整洁和一致性，而无需您每次都编写额外的代码。在本文中，我们将深入探讨 SQL 触发器的确切含义及其工作原理。我们还将通过一些示例探索不同类型的 SQL 触发器，并了解它们在 MySQL、PostgreSQL 和 SQL Server 中的不同用法。最后，您将对在数据库设置中如何以及何时实际使用触发器有一个很好的了解。

什么是SQL触发器？

触发器就像一个与数据库表绑定的自动程序，当特定事件（例如插入、更新或删除行）发生时，它会自动运行 SQL 代码。例如，您可以使用触发器自动设置创建、添加或删除新行的时间戳，或者在应用程序中无需额外代码即可应用新的数据规则。简单来说，我们可以说触发器是一组存储的 SQL 语句，它们会响应表事件而“触发”。

触发器在SQL中的工作原理

在 中，触发器使用 CREATE TRIGGER 语句定义，并附加到特定的表和事件。每个触发器都是行级的，这意味着它会对受事件影响的每一行运行一次。创建触发器时，您需要指定：

• 时间：BEFORE 或 AFTER – 触发器是在事件之前还是之后触发。
  事件：INSERT、UPDATE 或 DELETE – 激活触发器的操作。
  表：它所附加到的表的名称。
  触发器主体：要执行的 SQL 语句，包含在 BEGIN … END 中。

例如，BEFORE INSERT 触发器在新行添加到表之前运行，而 AFTER UPDATE 触发器在现有行更改后立即运行。MySQL 要求触发器中使用关键字 FOR EACH ROW，这使得它会对受操作影响的每一行执行触发器主体。

在触发器内部，使用 NEW 和 OLD 别名引用行数据。在 INSERT 触发器中，只有 NEW.column 可用（传入数据）。同样，在 DELETE 触发器中，只有 OLD.column 可用（关于要删除的行的数据）。但是，在 UPDATE 触发器中，您可以同时使用两者：OLD.column 引用更新前的行值，NEW.column 引用更新后的值。

让我们看看触发器的 SQL 语法：

CREATE TRIGGER trigger_name
BEFORE|AFTER {INSERT|UPDATE|DELETE} ON table_name
FOR EACH ROW
BEGIN
-- SQL statements here --
END;

这是标准的 SQL 格式。需要注意的是，触发器主体通常包含多个带分号的语句；通常应该先更改 SQL 分隔符，例如更改为 //，这样才能正确解析整个 CREATE TRIGGER 语句块。

创建触发器的分步示例

现在让我们看看如何在 SQL 中创建触发器。

步骤 1：准备表

为此，我们先创建一个简单的用户表：

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
created_at DATETIME,
updated_at DATETIME
);

步骤 2：更改分隔符

在 SQL 中，您可以更改语句分隔符，以便编写多语句触发器。例如：

DELIMITER //

步骤 3：编写CREATE TRIGGER语句

例如，我们可以创建一个触发器，将 created_at 列设置为插入时的当前时间：

CREATE TRIGGER before_users_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END;
//

因此，在上面的代码中，BEFORE INSERT ON users 表示触发器在插入每行新数据之前触发。触发器主体会检查 NEW.created_at 是否为空，如果是，则使用 NOW() 填充。这会自动设置时间戳。

编写触发器后，您可以根据需要恢复分隔符，以便其他代码可以顺利执行。

DELIMITER ;

步骤 4：测试触发器

现在，当您插入数据时未指定 created_at，触发器将自动设置。

INSERT INTO users (username) VALUES ('Alice');
SELECT * FROM users;

并且 created_at 将自动填充当前日期/时间。触发器可以通过设置默认值来自动执行任务。

不同类型的触发器

每个表有六种类型的 SQL 触发器：

1. BEFORE INSERT trigger（插入前触发器）
2. BEFORE UPDATE Trigger（更新前触发器）
3. BEFORE DELETE Trigger（删除前触发器）
4. AFTER INSERT Trigger（插入后触发器）
5. AFTER UPDATE Trigger（更新后触发器）
6. AFTER DELETE Trigger（删除后触发器）

让我们通过示例来了解每种触发器。

1. 插入前触发器

此触发器在新行插入表之前激活。它通常用于在保存数据之前验证或修改数据。

BEFORE INSERT 触发器的 SQL 语法示例：

DELIMITER //
CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;
// 
DELIMITER ;

在插入新用户记录之前，此触发器会自动将 created_at 时间戳设置为当前时间。

2. 更新前触发器

此触发器在现有行更新之前执行。这允许在更新发生之前验证或修改数据。

BEFORE UPDATE 触发器的 SQL 语法示例：

DELIMITER //
CREATE TRIGGER before_update_user
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.email NOT LIKE '%@%' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email address';
  END IF;
END;
//
 
DELIMITER ;

此触发器在更新用户记录之前检查新的电子邮件地址是否有效。如果无效，则会引发错误。

3. 删除前触发器

此触发器在删除行之前执行。也可用于强制引用完整性或在特定条件下阻止删除。

BEFORE DELETE 触发器的 SQL 语法示例：

DELIMITER //
CREATE TRIGGER before_delete_order
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status = 'Shipped' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders';
  END IF;
END;
//
 
DELIMITER ;

此触发器可防止删除已发货的订单。

4. 插入后触发器

此触发器在插入新行后执行，通常用于记录日志或更新相关表。

AFTER INSERT 触发器 SQL 语法示例：

DELIMITER //
CREATE TRIGGER after_insert_user
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time
  VALUES (NEW.id, 'User created', NOW());
END;
//
 
DELIMITER ;

此触发器会在 user_logs 表中记录新用户的创建。

5. 更新后触发器

此触发器在行更新后执行。它可用于审计更改或更新相关数据。

AFTER UPDATE 触发器的 SQL 语法示例：

DELIMITER //
CREATE TRIGGER after_update_user
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (NEW.id, CONCAT('User updated: ', OLD.name, ' to ', NEW.name), NOW());
END;
//
 
DELIMITER ;

此触发器记录更新后用户名的更改。

6. 删除后触发器

此触发器在删除行后执行。通常用于记录删除操作或清理相关数据。

AFTER DELETE 触发器的 SQL 语法示例：

DELIMITER //
CREATE TRIGGER after_delete_user
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action, log_time)
  VALUES (OLD.id, 'User deleted', NOW());
END;
//
 
DELIMITER ;

此触发器将用户的删除操作记录在 user_log 表中。

何时以及为何使用触发器

当您想要自动执行数据更改时发生的操作时，触发器非常有用。以下是一些用例和优势，重点介绍了何时以及为何应该使用 SQL 触发器。

• 日常任务自动化：您可以自动填充或更新时间戳、计数器或某些计算值等列，而无需在应用中编写任何额外代码。如上例所示，我们自动使用了 created_at 和 updated_at 字段。
• 强制执行数据完整性和规则：触发器可以帮助您检查条件，甚至防止无效操作。例如，如果某行违反某些规则，BEFORE_INSERT 触发器会引发错误并停止该行。这确保即使发生错误，数据也能保持干净。
• 审计日志和跟踪：它们还可以帮助您自动记录更改。AFTER DELETE 触发器可以在删除某行时将记录插入日志表。这样无需编写单独的脚本即可提供审计线索。
• 保持多表一致性：有时，您必须遇到这样的情况：当一个表发生更改时，您希望另一个表也自动更新。触发器可以在后台处理这些关联的更新。

性能考虑和限制

您必须谨慎运行触发器。由于触发器每次数据更改时都会静默运行，因此如果触发器过多，它们有时可能会降低速度或使调试变得棘手。不过，对于设置时间戳、检查输入或同步其他数据等操作，触发器非常有用。它们可以节省时间，并减少因重复编写相同代码而犯下的愚蠢错误。

在决定使用 SQL 触发器之前，需要考虑以下几点：

• 隐藏逻辑：触发器代码存储在数据库中并自动运行，这可能会降低系统行为的透明度。因此，开发人员可能会忘记触发器在后台更改数据。因此，应该对其进行良好的文档记录。
• 无事务控制：您无法在 SQL 触发器中启动、提交或回滚事务。所有触发器操作都发生在原始语句的事务上下文中。换句话说，您无法在触发器中提交部分更改并继续执行主语句。
• 非事务性表：如果您使用非事务性引擎，则可能会发生触发器错误。SQL 无法完全回滚。因此，某些数据部分可能会更改，而某些部分可能不会更改，这可能会导致数据不一致。
• 受限数据操作：SQL 限制触发器执行某些语句。例如，您无法执行 DDL 或调用返回结果集的存储例程。此外，SQL 中没有视图触发器。
  无递归：SQL 不允许递归；它不能以会导致自身立即再次触发的方式不断修改定义它的同一张表。因此，建议避免设计通过不断更新相同行而循环的触发器。

MySQL、PostgreSQL和SQL Server触发器对比表

现在让我们来看看 MySQL、 和 SQL Server 等不同数据库上的触发器有何不同。

特性	MySQL	PostgreSQL	SQL Server
触发器语法	在 CREATE TRIGGER 中内联定义，采用 SQL 编写。始终包含 FOR EACH ROW。	使用 CREATE TRIGGER … EXECUTE FUNCTION function_name()。支持 FOR EACH ROW 和 FOR EACH STATEMENT。	使用 CREATE TRIGGER 并指定 AFTER 或 INSTEAD OF。始终为语句级触发器。使用 BEGIN … END。
粒度	仅支持行级（FOR EACH ROW）。	支持行级（默认）或语句级。	仅支持语句级。
时机选项	对 INSERT、UPDATE、DELETE 支持 BEFORE、AFTER。不支持 INSTEAD OF，也不能在视图上创建触发器。	支持 BEFORE、AFTER、INSTEAD OF（可在视图上使用）。	支持 AFTER、INSTEAD OF（可在视图上或用于覆盖操作）。
触发频率	每个受影响的行触发一次。	可按行触发一次，也可按语句触发一次。	每个语句触发一次。使用 inserted 和 deleted 虚拟表。
引用变更行	使用 NEW.column 和 OLD.column。	在触发函数内部使用 NEW 和 OLD。	使用 inserted 和 deleted 虚拟表，需将其 JOIN 后才能访问变更的行。
语言支持	仅支持 SQL（触发器内不支持动态 SQL）。	支持 PL/pgSQL、PL/Python 等多种语言。支持动态 SQL，可 RETURN NEW/OLD。	使用 T-SQL，具有完整语言支持（事务、TRY/CATCH 等）。
功能能力	功能简单。不支持动态 SQL 或返回结果集的存储过程。BEFORE 触发器可修改 NEW。	功能强大。可中止或修改操作、返回值，并可使用多种语言。	与 SQL Server 功能深度集成。支持 TRY/CATCH、事务及复杂逻辑。
触发器数量限制	在 v5.7.2 之前：每个表每个事件（INSERT、UPDATE、DELETE）仅允许 1 个 BEFORE 和 1 个 AFTER 触发器；<br>在 v5.7.2 及之后，允许为同一事件和时机创建多个触发器，并通过 FOLLOWS / PRECEDES 控制顺序。	不限制触发器数量。	每个表最多允许 16 个触发器。
触发顺序控制	可使用 FOLLOWS / PRECEDES 控制触发器执行顺序。	不支持原生触发顺序控制。	不支持原生顺序控制，但可在触发器内部通过逻辑实现。
错误处理	不支持 TRY/CATCH。错误会中止语句。AFTER 仅在 BEFORE 和行级操作成功后执行。	在函数中使用 EXCEPTION 块。错误中止语句。	支持 TRY/CATCH。触发器中的错误会中止语句。

小结

虽然 SQL 触发器一开始可能感觉有点复杂，但一旦上手，你就会完全理解它们，并体会到它们的实用性。当表中发生更改时，它们会自行运行，从而节省时间并确保数据持续遵循你设置的规则。无论是记录更改、阻止不必要的更新，还是跨表同步信息，触发器在 SQL 中都非常有用。但请确保不要过度使用触发器，也不要创建太多触发器，因为这会使事情变得混乱，并且以后难以调试。保持简单，进行适当的测试，这样你就可以开始了。