SQL Trigger详解

发布时间：2023-05-19

一、什么是SQL Trigger

SQL Trigger，即SQL触发器，是一种特殊的数据库对象，与表有关联，可以在特定的操作（例如修改数据、插入新数据或删除数据）发生时自动执行。SQL Trigger可以用来保证数据的完整性，实现业务逻辑的自动化，以及记录数据的变化历史等。

二、SQL Trigger的语法

SQL Trigger有以下基本的语法：

CREATE TRIGGER trigger_name [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
FOR EACH ROW 
BEGIN
-- sql statement
END;

其中：

  • trigger_name：触发器的名称。
  • BEFORE|AFTER：指定在操作之前还是之后触发该触发器。
  • INSERT|UPDATE|DELETE：指定在执行插入、更新或删除操作时触发该触发器。
  • table_name：指定绑定该触发器的数据库表名称。
  • FOR EACH ROW：指定该触发器是针对每一行数据而触发，而不是每个操作。
  • BEGIN...END：包括要执行的SQL语句。

三、SQL Trigger的应用场景

1. 数据完整性的维护

SQL Trigger可以用来在某些操作发生时自动校验数据的完整性，从而避免数据不一致的情况出现。 例如，一个在线商店的商品库存不能为负数，可以创建一个触发器，规定在商品售出之前，先判断库存数量是否满足要求，在不满足的情况下阻止交易事件的发生。

2. 业务逻辑的自动化

SQL Trigger可以用来自动化处理某些业务逻辑，避免人工操作带来的错误和延迟。 例如，一个银行的客户在转账时，需要记录交易的发起时间和完成时间，可以创建一个触发器，在转账开始时插入一条记录，并在转账结束时更新该记录的完成时间。

3. 数据变化历史的记录

SQL Trigger可以用来记录数据的变化历史，以便在数据出现问题时进行回溯和排查。 例如，一个人事管理系统需要记录员工的调职记录，可以创建一个触发器，在员工信息表中的职位字段发生变化时，自动在调职记录表中新增一条记录，记录调职时间、调职前的职位和调职后的职位等信息。

四、SQL Trigger的示例

1. 数据完整性的维护

以下示例通过创建一个触发器，防止商品库存数量为负数。

CREATE TRIGGER check_inventory BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.inventory < 0 THEN
    SET NEW.inventory = OLD.inventory;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Inventory cannot be negative.';
  END IF;
END;

以上SQL语句创建了一个名为check_inventory的触发器，该触发器针对products表的每个更新操作，在更新之前检查inventory字段的值是否小于0，如果是则阻止该更新行为。

2. 业务逻辑的自动化

以下示例通过创建一个触发器，在银行转账时自动处理交易时间的记录。

CREATE TRIGGER update_transaction_time AFTER UPDATE ON bank_accounts
FOR EACH ROW
BEGIN
  IF NEW.balance <> OLD.balance THEN
    UPDATE transaction_history SET completion_time = NOW()
    WHERE account_number = NEW.account_number AND completion_time IS NULL;
  END IF;
END;

以上SQL语句创建了一个名为update_transaction_time的触发器，该触发器针对bank_accounts表的每个更新操作，在balance字段发生变化时，自动更新对应的交易记录的完成时间。

3. 数据变化历史的记录

以下示例通过创建一个触发器，在员工调职时自动记录调职记录。

CREATE TRIGGER insert_transfer_record AFTER UPDATE ON employee_info
FOR EACH ROW
BEGIN
  IF NEW.position <> OLD.position THEN
    INSERT INTO transfer_history (employee_id, transfered_time, from_position, to_position)
    VALUES (NEW.id, NOW(), OLD.position, NEW.position);
  END IF;
END;

以上SQL语句创建了一个名为insert_transfer_record的触发器，该触发器针对employee_info表的每个更新操作，在position字段发生变化时，自动在transfer_history表中创建一条记录，记录转移员工的ID、时间、原职位和现职位。

推荐文章