MSSQL触发器执行失败：一场惨痛的教训

1. 引言

触发器（Trigger）是 SQL Server 中的一种特殊对象，可以对指定的表进行自动化的增、删、改、查操作的事务性处理。触发器可以在数据插入、更新或删除数据时自动执行一个所定义的程序（Transact-SQL语句和托管代码）。

然而，若触发器不正确地编写，将可能导致程序不可预测的错误和异常。本文将分享一次关于 MSSQL 触发器执行失败的教训。

2. 教训

2.1 问题描述

我们在 MSSQL 数据库中创建了一个名为 my_table 的表，该表包含以下字段：id、name、age、address。

我们还创建了一个名为 tr_my_table 的触发器，用于在每次向该表插入数据时向另一张表 my_table_log 中写入一条日志记录。

CREATE TRIGGER [dbo].[tr_my_table]
  ON [dbo].[my_table]
  AFTER INSERT
  AS
  BEGIN
    INSERT INTO my_table_log (name, age, address) VALUES (i.name, i.age, i.address)
  END

但是，当我们使用 SQL Server Management Studio 向 my_table 表中插入一条记录时，突然发现无法提交并出现下面的错误提示：

Msg 50000, Level 16, State 1, Procedure tr_my_table, Line 4 [Batch Start Line 0]
Execution failed for Trigger 'tr_my_table'. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
The statement has been terminated.

2.2 问题探究

当我们在 SQL Server Management Studio 中执行一条 SQL 语句时，该操作将被作为一个事务进行处理。每个事务必须要有一个明确的开始（BEGIN）和结束（COMMIT or ROLLBACK）。但是，在上述触发器代码中，并没有包含任何的开始和结束事务的语句。

  BEGIN  -- 缺少
    INSERT INTO my_table_log (name, age, address) VALUES (i.name, i.age, i.address)
  END  -- 缺少

此外，我们还发现，触发器代码中的一个变量 i 没有被定义和初始化，这个问题也需要解决。

2.3 问题解决

为了解决以上问题，我们需要向触发器代码中添加起始和结束语句以及对变量 i 进行定义和初始化。

CREATE TRIGGER [dbo].[tr_my_table]
  ON [dbo].[my_table]
  AFTER INSERT
  AS
  BEGIN TRANSACTION  -- 添加事务的起始语句
    DECLARE @name VARCHAR(50), @age INT, @address VARCHAR(100)
    SELECT @name = name, @age = age, @address = address FROM inserted
    INSERT INTO my_table_log (name, age, address) VALUES (@name, @age, @address)
  COMMIT TRANSACTION  -- 添加事务的结束语句
END

修改后的代码中，我们使用 BEGIN TRANSACTION 和 COMMIT TRANSACTION 来定义事务的开始和结束。

同时，我们对 i 变量进行了定义和初始化的操作，使用了 MSSQL 提供的内置表 inserted 来解决。

3. 总结

本文介绍了一次 MSSQL 触发器执行失败的教训，该教训提醒我们需要在编写触发器代码时注意事务的处理和变量的定义和初始化操作。通过本文的介绍和解决方法，相信读者可以更好地了解 MSSQL 中触发器的使用方法，为大家提供参考和学习指导。