主页 » SQL 触发器

SQL 触发器

摘要：在本教程中，您将了解 SQL 触发器概念以及如何在数据库系统中开发简单的触发器。

SQL 触发器简介

触发器是在数据库表中发生特定事件后自动执行的一段代码。

触发器始终与特定表关联。如果删除表，则所有关联的触发器也会自动删除。

触发器要么在以下事件之前要么之后调用

当您发布INSERT、UPDATE或DELETE语句时，关系数据库管理系统 (RDBMS) 会触发相应的触发器。

在某些 RDMBS 中，还会在执行调用INSERT、UPDATE或DELETE语句的语句的结果中调用触发器。例如，MySQL 具有 LOAD DATA INFILE，该功能可从文本文件中读取行并以极高的速度将其插入表中，调用BEFORE INSERT和AFTER INSERT触发器。

另一方面，语句可能会删除表中的行，但不会调用关联的触发器。例如，TRUNCATE TABLE 语句会删除表中的所有行，但不会调用BEFORE DELETE和AFTER DELETE触发器。

触发器创建语句语法

要创建触发器，请使用以下语句

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)

行级触发器与语句级触发器

有两种类型的触发器：行级触发器和语句级触发器。

行级触发器在UPDATE语句影响行时每次执行。如果UPDATE语句影响 10 行，则行级触发器将执行 10 次，每次针对一行。如果UPDATE语句不影响任何行，则行级触发器根本不会执行。

与行级触发器不同，语句级触发器会被调用一次，无论UPDATE语句影响多少行。请注意，如果UPDATE语句未影响任何行，则触发器仍将执行。

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

SQL 触发器用法

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

  • 记录表修改。某些表具有敏感数据，如客户电子邮件、员工薪资等，您需要记录所有更改。在这种情况下，您可以创建 UPDATE 触发器，将更改插入单独的表中。
  • 实施复杂数据完整性。在此场景中，您可以定义触发器，验证数据并在必要时重新设置数据格式。例如，可以使用 BEFORE INSERT 或 BEFORE UPDATE 触发器，在插入或更新前转换数据。

SQL 触发器示例

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

employees_table

假设我们要记录 salary 列中值的更改。为此，我们创建一个单独的表 来存储更改，并使用触发器将更改插入此表中。

以下语句会创建 salary_changes 表。

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at DATETIME 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 列使用当前时间作为默认值，记录发生更改的时间。

以下 before_update_salary 触发器将薪资更改记录到 salary_changes 表中。

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
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;
END;
Code language: SQL (Structured Query Language) (sql)

在触发器的正文中，如果新薪资不同于旧薪资，我们会插入更改。

请注意，在触发器正文内，我们使用 OLD 和 NEW 关键字来访问触发器影响的行中的列。

让我们通过将 ID 为 102 的员工的薪资提高 5% 来测试触发器。

首先，检查员工 102 的当前薪资

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

其次，通过发出以下 UPDATE 语句将薪资提高 5%。

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

第三，检查 salary_changes 表，以查看是否已调用触发器。

SELECT 
    *
FROM
    salary_changes;Code language: SQL (Structured Query Language) (sql)
SQL Trigger Log Table

如您所见，salary_changes 表有一个新条目。这意味着触发器已正确调用。

修改触发器

要更改触发器定义，请使用 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)

删除触发器

要删除触发器，请使用 DROP TRIGGER 语句，如下所示

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

IF EXISTS 选项允许您在触发器存在时将其删除。如果触发器不存在，则此语句不会执行任何操作。但是，如果您不使用 IF EXISTS 选项，当您尝试删除不存在的触发器时，数据库系统可能会发出错误。

同样，如果您删除某个表，则与此表关联的所有触发器也将被删除。以下语句会删除 before_update_salary 触发器

DROP TRIGGER IF EXISTS before_update_salary;Code language: SQL (Structured Query Language) (sql)

现在，您应该对 SQL 触发器有了很好的理解，并且知道如何在数据库系统中创建触发器。

SQL Views
以前
SQL 视图
下一步
SQL 函数
SQL Functions