🐻SQL触发器

sql
10

触发器简介与代码演示

SQL触发器（SQL triggers）是数据库中的一种特殊类型的存储过程，用于在指定的数据库事件发生时自动执行特定的操作。它们主要的作用包括：

  1. 维护数据的完整性：触发器可以帮助确保数据的完整性和一致性。通过在特定表上设置触发器，可以在数据插入、更新或删除时执行相关的检查或操作，确保数据满足特定的约束条件。
  2. 自动化常规任务：它们能够在某些特定事件发生时自动执行指定的任务。例如，在数据修改后，触发器可以用于更新其他相关表中的数据，或者在数据插入后向其他系统发送通知。
  3. 实现业务规则：通过触发器，可以在数据库级别上实现特定的业务规则和逻辑。这有助于确保数据库中的数据和操作遵循特定的业务流程和规范。
  4. 监控和审计：触发器可以用于监控数据库中的操作，并记录这些操作。这对于审计和跟踪数据库中发生的变化非常有用。
  5. 实现联级操作：当在一个表上进行操作时（例如插入、更新或删除记录），触发器可以触发另一个表上的相关操作。这种联级操作有助于维护数据库的一致性和相关性。

值得注意的是，触发器中的 inserted 和 deleted 是 SQL Server 中的两个特殊虚拟表。inserted 表存储了在触发 INSERT 操作时新增的行数据，而 deleted 表则存储了在触发 DELETE 操作时删除的行数据。对于 UPDATE 操作，inserted 存储新的数据，而 deleted 存储旧的数据。

当创建 SQL Server 触发器时，需要定义触发器的类型（AFTER 或 INSTEAD OF）、触发事件（INSERT、UPDATE、DELETE）、与其关联的表以及触发器的逻辑。

首先当创建触发器时，可以根据需要定义相应的逻辑。以下是一个示例，展示如何创建一个简单的触发器，用于在数据插入时将相应的记录插入到另一个表中。

首先，我们创建两张表：Employee 和 Employee_Log。Employee 表包含员工信息，Employee_Log 表用于记录每次插入操作的日志。

-- 创建 Employee 表
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- 创建 Employee_Log 表用于记录日志
CREATE TABLE Employee_Log (
    LogID INT PRIMARY KEY,
    Action VARCHAR(50),
    EmployeeID INT,
    LogDateTime DATETIME,
    CONSTRAINT FK_Employee_Log FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

接下来，创建一个 AFTER INSERT 触发器，在每次向 Employee 表中插入新数据时，自动将操作记录插入到 Employee_Log 表中。

-- 创建触发器
CREATE TRIGGER trgEmployeeInsert
ON Employee
AFTER INSERT
AS
BEGIN
    DECLARE @EmployeeID INT;
    DECLARE @EmployeeName VARCHAR(50);
    DECLARE @LogDateTime DATETIME = GETDATE();

    -- 获取刚插入的员工信息
    SELECT @EmployeeID = EmployeeID, @EmployeeName = EmployeeName
    FROM inserted;

    -- 插入记录到日志表
    INSERT INTO Employee_Log (Action, EmployeeID, LogDateTime)
    VALUES ('Employee Inserted: ' + @EmployeeName, @EmployeeID, @LogDateTime);
END;

现在，每当向 Employee 表中插入新的员工记录时，触发器将自动记录相应的操作并将其插入到 Employee_Log 表中。

请注意，这只是一个简单的示例，实际的触发器逻辑可能更复杂，根据业务需求进行不同的操作和记录。同时，触发器的使用需要谨慎，确保逻辑正确并考虑性能影响。

复杂的触发器

在 SQL Server 中，触发器（Triggers）可以包含复杂的流程，例如在触发器中执行多个操作、处理不同条件下的逻辑等。下面是一个简单示例，展示了一个包含条件判断和多个操作的触发器。

假设有两个表：Orders 和 Orders_Log，Orders 表存储订单信息，Orders_Log 用于记录订单操作日志。我们希望在订单表发生 INSERT、UPDATE 或 DELETE 操作时，记录相关操作到日志表中。

首先，创建表 Orders 和 Orders_Log：

-- 创建 Orders 表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    OrderAmount DECIMAL(10, 2)
);

-- 创建 Orders_Log 表
CREATE TABLE Orders_Log (
    LogID INT PRIMARY KEY,
    Action VARCHAR(50),
    OrderID INT,
    LogDateTime DATETIME,
    CONSTRAINT FK_Orders_Log FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

接下来，创建触发器 trgOrdersActions，它将在 Orders 表上的 INSERT、UPDATE 或 DELETE 操作之后触发。

-- 创建触发器
CREATE TRIGGER trgOrdersActions
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Action VARCHAR(50);
    DECLARE @OrderID INT;
    DECLARE @LogDateTime DATETIME = GETDATE();

    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        IF EXISTS (SELECT * FROM deleted)
        BEGIN
            -- 如果同时有删除和插入数据，表示有更新操作
            SET @Action = 'Update';
            SELECT @OrderID = OrderID FROM inserted;
        END
        ELSE
        BEGIN
            -- 仅有插入数据，表示有新增操作
            SET @Action = 'Insert';
            SELECT @OrderID = OrderID FROM inserted;
        END
    END
    ELSE
    BEGIN
        -- 仅有删除数据，表示有删除操作
        SET @Action = 'Delete';
        SELECT @OrderID = OrderID FROM deleted;
    END

    -- 记录操作到日志表中
    INSERT INTO Orders_Log (Action, OrderID, LogDateTime)
    VALUES (@Action, @OrderID, @LogDateTime);
END;

在这个触发器示例中，我们使用了 inserted 和 deleted 虚拟表，通过判断这些表中的存在情况来确定触发器的操作类型（INSERT、UPDATE、DELETE）。然后根据操作类型将相应的信息记录到 Orders_Log 表中。

执行这段 SQL 代码后，当对 Orders 表进行 INSERT、UPDATE 或 DELETE 操作时，触发器会自动记录相应的操作信息到 Orders_Log 表中。这是一个简单示例，实际情况下触发器的逻辑可能更加复杂，根据业务需求设计不同的流程和操作。