SQL Server触发器和事务用法示例

1. 什么是SQL Server触发器

SQL Server触发器是一种特殊类型的存储过程，当在一个表上执行特定的操作时（例如INSERT、UPDATE或DELETE），它们会自动激活。它们可以实现数据完整性检查、在记录被插入或修改前检查数据、防止重复数据的插入、更新数据、以及自动给表添加信息等功能。

2. 创建触发器

2.1 创建Before Insert触发器

这是一个在插入数据进行之前执行逻辑的示例：

CREATE TRIGGER Test_Trig_Upd
ON dbo.Test_Table
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;


SELECT 'The below records have been inserted in Test_Table' AS MsgBody, * FROM inserted;
END

上面代码意思是在在Test_Table表在插入新数据之后执行所定义的SQL命令，包含表格插入之前的状态和表格插入之后的状态，以及"SET"语句和其他命令。

2.2 创建Instead of insert 触发器

一个Instead of insert 触发器可以用来代替Insert命令，对任何由一方表存储副本的表具有很好的作用，示例代码如下：

CREATE TRIGGER Trigger_Test_Table_Upd
ON dbo.Test_Table
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.Test_Table ( 
        ID,
        Name)
    SELECT 
        insrt.ID,
        insrt.Name
    FROM inserted insrt
END

3. 触发器中使用事务

当触发器被触发时，我们经常希望对数据进行同步操作，并确保在出现任何错误时对数据操作进行回滚，保证数据的一致性和有效性。这需要我们使用事务来处理我们的数据操作，而触发器会在使用事务时 发挥重要作用。

3.1 使用事务在Before Insert触发器中执行

以下是在一个事务中在SameTable表中插入数据并在另一个表中插入通知的示例代码：

CREATE TRIGGER Trigger_SameTable_Insert 
ON dbo.SameTable 
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            INSERT INTO dbo.NotificationTable
            SELECT ID AS NotificationID, 
               'Table SameTable has had data inserted' AS NotificationMsg
            FROM inserted
            COMMIT TRANSACTION
        END TRY
        
        BEGIN CATCH
            IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        END CATCH
END

3.2 使用事务在Instead Of Insert 触发器中执行

以下是在一个事务中在SameTable表插入数据的示例代码：

CREATE TRIGGER Trigger_SameTable_Instead_Insert  
ON dbo.SameTable
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            INSERT INTO dbo.SameTable
            (ID, Data1, Data2, Data3)
            SELECT 
                ID, 
                Data1, 
                Data2, 
                Data3
            FROM inserted;
        COMMIT TRANSACTION
    END TRY
    
    BEGIN CATCH
        IF @@TRANCOUNT > 0 
            ROLLBACK TRANSACTION
    END CATCH  
END

4. 总结

我们可以看到，SQL Server中的触发器可以增加到SQL Server的表中。触发器可以执行一些特定的动作，例如写日志、检查完整性、限制用户的更改、自动更新信息等等操作。通过触发器，我们可以自动执行业务逻辑，从而更好地维护数据的完整性和有效性。当我们在执行数据操作时，很重要处理事务以确保我们的数据操作真正符合我们的期望。