SQL sever 触发器和两张特殊的临时表

触发器（Trigger）是一种与表相关联的特殊类型的存储过程，它会在指定的事件发生时自动执行一些操作。触发器可以在以下情况下自动触发：

• 当在表上执行 INSERT、UPDATE 或 DELETE 操作时
• 当启动或停止服务器时
• 当创建、修改或删除其他对象时

SQL Server 支持两种类型的触发器：AFTER 触发器和 INSTEAD OF 触发器。在触发器的执行顺序中，INSTEAD OF 触发器优先于AFTER 触发器。

1. AFTER 触发器

AFTER 触发器可以在 INSERT、UPDATE 或 DELETE 操作后自动执行，用于实现对数据更新的后续处理。在创建 AFTER 触发器时可以指定何时执行该触发器：对于 INSERT 操作，可以在新行插入到表后执行触发器；对于 UPDATE 操作，可以在行更新后执行触发器；对于 DELETE 操作，可以在行从表中删除后执行触发器。

下面是一个示例：

CREATE TRIGGER MyTrigger 
ON MyTable 
AFTER INSERT, UPDATE, DELETE 
AS 
BEGIN 
    -- 在此处放置触发器体逻辑
END;

1. INSTEAD OF 触发器

INSTEAD OF 触发器可以在执行 INSERT、UPDATE 或 DELETE 操作时代替默认的操作，通过自定义逻辑来控制操作的执行。通常，INSTEAD OF 触发器用于实现视图、计算列和多表插入等高级数据操作。

下面是一个示例：

CREATE TRIGGER MyTrigger 
ON MyView 
INSTEAD OF INSERT, UPDATE 
AS 
BEGIN 
    -- 在此处放置触发器体逻辑
END;

触发器可以访问两种特殊的临时表：INSERTED 和 DELETED，这两张临时表记录了在触发器中进行 INSERT、UPDATE 或 DELETE 操作所影响到的行。

1. INSERTED

在执行 INSERT、UPDATE 操作时，INSERTED 临时表记录了插入或更新操作影响的行的副本。在 INSERT 操作中，INSERTED 包含全部插入的行；在 UPDATE 操作中，INSERTED 包含更新后的行。

下面是一个 UPDATE 操作时 INSERTED 临时表的示例：

UPDATE MyTable SET Column1 = 'NewValue' WHERE Column2 = 'Value';

在触发器中使用 INSERTED 临时表，可以抓取更新后的行并在触发器中进一步更新。

CREATE TRIGGER MyTrigger ON MyTable
AFTER UPDATE
AS
BEGIN
    DECLARE @UpdatedRows TABLE (Column1 nvarchar(50), Column2 nvarchar(50))

    INSERT INTO @UpdatedRows (Column1, Column2)
    SELECT i.* FROM INSERTED i
    JOIN deleted d ON i.PrimaryKey = d.PrimaryKey
    WHERE i.Column1 <> d.Column1

    UPDATE MyTable
    SET Column3 = GETDATE()
    WHERE EXISTS (SELECT * FROM @UpdatedRows WHERE Column2 = MyTable.Column2)
END

该触发器会在 MyTable 表中更新某些行时触发，使用 INSERTED 临时表捕获更新的行并根据需要进一步处理。

1. DELETED

在执行 UPDATE、DELETE 操作时，DELETED 临时表记录了更新或删除操作前的行的副本。在 DELETE 操作中，DELETED 包含全部删除的行；在 UPDATE 操作中，DELETED 包含更新前的行。

下面是一个 DELETE 操作时 DELETED 临时表的示例：

DELETE FROM MyTable WHERE Column1 = 'Value';

在触发器中使用 DELETED 临时表，可以抓取删除前的行并在触发器中进一步处理。

CREATE TRIGGER MyTrigger ON MyTable
AFTER DELETE
AS
BEGIN
    DECLARE @DeletedRows TABLE (Column1 nvarchar(50), Column2 nvarchar(50))

    INSERT INTO @DeletedRows (Column1, Column2)
    SELECT d.* FROM DELETED d

    INSERT INTO MyLogTable (ActionType, Column1, Column2)
    SELECT 'DELETE', Column1, Column2 FROM @DeletedRows
END

该触发器会在 MyTable 表中删除某些行时触发，使用 DELETED 临时表捕获删除前的行并将其插入到日志表中进行记录。

INSERTED 和 DELETED 临时表在触发器中有非常重要的作用，可以用于捕获和处理在更新过程中发生的变化，以及提高数据库操作的精度和效率。在编写触发器时，需要考虑如何使用这些临时表来实现所需的数据操作。

触发器的使用可以使数据库的逻辑更加简单和易于理解，同时也会给自动化处理带来方便。然而，过多地使用触发器可能会影响数据库的性能，因此应该尽可能地减少触发器的使用数量。另外，在编写触发器时也需要考虑触发器产生的副作用，以及其可能导致的数据一致性问题。