SQL 触发器

摘要：在本教程中，您将学习 SQL 触发器以及如何创建触发器，以响应表中的事件自动调用一段代码。

SQL 触发器简介 #

触发器是一种数据库对象，它会响应表中的特定事件，执行一段代码、一个用户定义的函数或一个存储过程。

触发器总是与特定的表相关联。如果表被删除，所有相关的触发器也会被自动删除。

触发器在以下事件之前或之后被调用：

INSERT – 当插入新行时
UPDATE – 当更新现有行时
DELETE – 当删除一行时。
TRUNCATE – 当表被截断时。(PostgreSQL)。

当您对表执行 INSERT、UPDATE 或 DELETE 语句时，如果存在相应的触发器，关系数据库管理系统 (RDBMS) 就会触发它。

在某些 RDBMS 中，执行间接执行 INSERT、UPDATE 或 DELETE 语句的语句也会调用触发器。

例如，MySQL 有 LOAD DATA INFILE 语句，它从文本文件中读取数据并以非常高的速度将其插入表中，这会调用 BEFORE INSERT 和 AFTER INSERT 触发器。

另一方面，有些语句可能会删除表中的行，但不会调用相关的触发器。

例如，TRUNCATE TABLE 语句会删除表中的所有行，但不会调用 BEFORE DELETE 和 AFTER DELETE 触发器。在 PostgreSQL 中，TRUNCATE TABLE 语句会触发一个 TRUNCATE 触发器。

创建触发器 #

要创建触发器，您需要使用以下语句：

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

在此语法中：

首先，在 CREATE TRIGGER 子句后指定触发器的名称。
其次，使用 BEFORE 或 AFTER 关键字来确定触发器应何时响应特定事件，例如 INSERT、UPDATE 或 DELETE。
然后，提供触发器关联的表的名称。
之后，使用 FOR EACH ROW 或 FOR EACH STATEMENT 子句定义触发器的类型。
最后，将触发器的逻辑放在 BEGIN ... END 块中。

除了在 BEGIN...END 块中使用代码，您还可以执行用户定义的函数或存储过程。

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name 
   trigger_type
EXECUTE stored_procedure_name;Code language: SQL (Structured Query Language) (sql)

行级触发器 vs. 语句级触发器 #

触发器有两种类型：

行级触发器
语句级触发器

行级触发器在每一行受到 DML 语句（如 INSERT、UPDATE 和 DELETE）影响时都会执行。

如果语句影响了 10 行，行级触发器将执行 10 次。如果语句没有影响任何行，行级触发器将不会执行。

与行级触发器不同，无论受影响的行数是多少，语句级触发器都只被调用一次。

请注意，如果 SQL 语句没有影响任何行，语句级触发器仍然会执行。

在创建触发器时，您可以使用 FOR EACH ROW 或 FOR EACH STATEMENT 子句分别指定触发器是行级还是语句级。

为何使用 SQL 触发器 #

您通常在以下场景中使用触发器：

日志记录。有些表包含敏感数据，如客户电子邮件、员工信息和薪水。您希望记录所有更改。在这种情况下，您可以创建一个 UPDATE 触发器，将更改记录到一个单独的表中。
强制实施复杂的数据完整性。您可能需要创建触发器，使用复杂的逻辑来验证数据，而这些逻辑无法通过简单的约束（如 NOT NULL、UNIQUE 和 CHECK 约束）来实现。

创建 SQL 触发器 #

我们将使用示例数据库中的 employees 表进行演示。

假设您想记录 employees 表中 salary 列值的更改。为此，您需要创建一个单独的表来存储这些更改，并使用触发器将更改插入到这个表中。

首先，创建 salary_changes 表以存储薪水更改：

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_salary DECIMAL(8 , 2 ),
    new_salary DECIMAL(8 , 2 ),
    PRIMARY KEY (employee_id , changed_at)
);Code language: SQL (Structured Query Language) (sql)

试一试

salary_changes 表记录了员工 ID、旧薪水、新薪水和更改时间。

请注意，change_at 列使用当前时间作为默认值，以记录更改发生的时间。

其次，创建一个函数，将更改记录到 salary_changes 表中：

CREATE OR REPLACE FUNCTION log_salary_changes () 
RETURNS TRIGGER 
AS 
$$
BEGIN
    IF NEW.salary != OLD.salary THEN
    	INSERT INTO salary_changes(employee_id,old_salary,new_salary)
        VALUES(NEW.employee_id,OLD.salary,NEW.salary);
    END IF;
    RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

试一试

请注意，在触发器主体内，您可以访问 OLD 和 NEW 变量，以分别访问更新前 (OLD) 和更新后 (NEW) 的行。

第三，创建一个名为 before_update_salary 的触发器，它执行 log_salary_changes 函数，将薪水更改记录到 salary_changes 表中：

CREATE TRIGGER salary_changes
AFTER UPDATE OF salary ON employees 
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes ();Code language: SQL (Structured Query Language) (sql)

试一试

第四，检索员工 ID 为 102 的当前薪水：

SELECT
  employee_id,
  first_name,
  last_name,
  salary
FROM
  employees
WHERE
  employee_id = 110;Code language: SQL (Structured Query Language) (sql)

 employee_id | first_name | last_name | salary
-------------+------------+-----------+---------
         110 | John       | Chen      | 8200.00

第四，使用以下 UPDATE 语句将员工 110 的薪水增加 5%：

UPDATE employees
SET
  salary = salary * 1.05
WHERE
  employee_id = 110;Code language: SQL (Structured Query Language) (sql)

最后，检查 salary_changes 表，看触发器是否已被调用：

SELECT
  employee_id,
  old_salary,
  new_salary
FROM
  salary_changes;Code language: SQL (Structured Query Language) (sql)

输出

 employee_id | old_salary | new_salary
-------------+------------+------------
         110 |    8200.00 |    8610.00

输出表明 salary_changes 表中有了一条新记录。这意味着触发器已正确调用。

修改 SQL 触发器 #

要更改触发器的定义，您可以使用 CREATE OR REPLACE TRIGGER 语句。

如果触发器不存在，CREATE OR REPLACE TRIGGER 会创建一个新的触发器；如果触发器已存在，则会修改它。

CREATE OR REPLACE TRIGGER 语句与 CREATE TRIGGER 语句类似，如下所示：

CREATE OR REPLACE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name 
   trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

移除 SQL 触发器 #

要从数据库中删除一个触发器，您可以使用 DROP TRIGGER 语句，其语法如下：

DROP TRIGGER [IF EXISTS] trigger_name;Code language: SQL (Structured Query Language) (sql)

在此语法中：

首先，在 DROP TRIGGER 关键字后指定触发器的名称 (trigger_name)。
其次，使用 IF EXISTS 选项仅在触发器存在时才删除它。如果触发器不存在，该语句将不执行任何操作。但是，如果您不使用 IF EXISTS 选项，当您尝试删除一个不存在的触发器时，数据库系统可能会报错。

例如，以下语句使用 DROP TRIGGER 语句移除 salary_changes 触发器：

DROP TRIGGER salary_changes;Code language: SQL (Structured Query Language) (sql)

摘要 #

触发器是与表关联的数据库对象，它会自动执行一段代码、一个用户定义的函数或一个存储过程，以响应表中发生的事件。
使用 CREATE TRIGGER 语句创建新触发器。
使用 CREATE OR REPLACE TRIGGER 语句替换触发器。
使用 DROP TRIGGER 语句移除触发器。