很多场景中，我们使用触发器来回滚一些不满足业务逻辑的修改，这没有问题，问题是我能在触发器中提交事务吗？
这个问题很小白，当也来测试一下
/*
测试中创建三种表,对表 TB2 插入时触发触发器,
在触发器中将TB2中新插入的数据插入到 TB3 和 TB4
*/
USE TestDB
GO
DROP TABLE TB2
GO
DROP TABLE TB3
GO
DROP TABLE TB4
GO
CREATE TABLE TB2
(
ID INT PRIMARY KEY IDENTITY,
C1 INT
)
GO
CREATE TABLE TB3
(
ID INT PRIMARY KEY,
C1 INT
)
GO
CREATE TABLE TB4
(
ID INT PRIMARY KEY,
C1 INT
)
GO
--=======================================
--创建触发器
CREATE TRIGGER utr_TB2_INSERT
ON dbo.TB2
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.TB3(ID,C1)
SELECT ID,C1 FROM inserted
COMMIT
INSERT INTO dbo.TB4(ID,C1)
SELECT ID,C1 FROM inserted
END
GO
--====================================
--在事务中尝试插入数据
BEGIN TRAN TR1
INSERT INTO TB2(ID,C1)
SELECT 1,1
SELECT @@TRANCOUNT
COMMIT
--====================================
--错误提示
/*
Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'TB2' when IDENTITY_INSERT is set to OFF.
*/
--====================================
--====================================
--在非事务中尝试插入数据
INSERT INTO TB2(ID,C1)
SELECT 1
--====================================
--错误提示
/*
Msg 120, Level 15, State 1, Line 3
The select list for the INSERT statement contains fewer items than the insert list.
The number of SELECT values must match the number of INSERT columns.
*/
--====================================
测试结论：
虽然可以创建出包含COMMIT语句的触发器，但是不应该在触发器中提交事务。
扩充：
如果不希望触发器中操作异常引发外部回滚，是否可以使用事务点来实现
--=======================================
--创建触发器
DROP TRIGGER [dbo].[utr_TB2_INSERT]
GO
CREATE TRIGGER utr_TB2_INSERT
ON dbo.TB2
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
SAVE TRANSACTION TR1;
BEGIN TRY
INSERT INTO dbo.TB3(ID,C1)
SELECT ID,C1 FROM inserted
INSERT INTO dbo.TB4(ID,C1)
SELECT ID,C1 FROM inserted
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION TR1
END CATCH
END
GO
--=======================================
--答案：不可以
/*
Msg 3931, Level 16, State 1, Procedure utr_TB2_INSERT, Line 17
The current transaction cannot be committed and cannot be rolled back to a savepoint.
Roll back the entire transaction.
*/