一、引言
SQL触发器是一种强大的工具，它能在数据库中的特定事件发生时自动执行一批SQL代码。它们特别适用于维护数据完整性、自动化管理任务以及跟踪数据库表中的变更。
二、SQL触发器简介
2.1 什么是SQL触发器？
SQL触发器是一种特殊的存储过程，它会在特定表或视图上的某些事件（如INSERT、UPDATE或DELETE操作）发生时自动执行（或“触发”）。
2.2 SQL触发器的组成部分
-
触发器名称：用于唯一标识触发器的名称。 -
触发时机：指定触发器何时触发（例如，在...之后、在...之前）。 -
触发事件：激活触发器的数据库操作（INSERT、UPDATE、DELETE）。 -
触发动作：触发器被触发时执行的SQL代码。
三、示例场景
假设我们有一个名为inventory.products的表，用于存储产品详情。我们希望通过将变更记录到product_log表中来跟踪对这个表所做的所有更改。
3.1 创建日志表
首先，创建一个表来存储日志条目。这个表将捕获对产品表所做的任何更改的详细信息。
CREATE TABLE inventory.product_log (
log_id INT IDENTITY(1,1) PRIMARY KEY,
action_type NVARCHAR(50),
product_id INT,
product_name VARCHAR(255),
quantity INT,
price DECIMAL(18, 2),
change_date DATETIME DEFAULT GETDATE()
);
-
log_id：每个日志条目的唯一标识符。 -
action_type：描述发生的操作类型（INSERT、UPDATE）。 -
product_id：被更改的产品的ID。 -
product_name：产品的名称。 -
quantity：产品的数量。 -
price：产品的价格。 -
change_date：发生更改的日期和时间。
3.2 创建触发器
接下来，创建一个触发器来记录对产品表的更改。
CREATE TRIGGER trg_productLog
ON inventory.products
AFTER INSERT, UPDATE
AS
BEGIN
-- 记录插入的记录
INSERT INTO inventory.product_log (
action_type,
product_id,
product_name,
quantity,
price
)
SELECT
CASE
WHEN EXISTS (SELECT * FROM inserted i WHERE i.product_id IS NOT NULL)
THEN 'INSERT'
ELSE 'UPDATE'
END AS action_type,
i.product_id,
i.product_name,
i.quantity,
i.price
FROM inserted i;
END;
-
AFTER INSERT, UPDATE：触发器将在产品表中插入或更新行后触发。 -
INSERTED表：这个系统定义的表包含了来自产品表的新行或更新行。 -
action_type：确定操作是INSERT还是UPDATE。
3.3 测试触发器
为了测试触发器，在产品表中插入或更新一行。
-- 插入新产品
INSERT INTO inventory.products
(product_id, product_name, quantity, price)
VALUES
(1, 'Product A', 100, 19.99);
-- 更新现有产品
UPDATE inventory.products
SET quantity = 150,
price = 17.99
WHERE product_id = 1;
运行这些语句后，检查product_log表以查看日志条目。
SELECT *
FROM inventory.product_log;
四、结论
SQL触发器对于自动化和跟踪数据库内的变更非常宝贵。通过上述步骤，您可以设置一个触发器来记录表中的更改，帮助您保持数据修改的清晰历史，并增强您管理和审计数据库的能力。