很多场景中，我们使用触发器来回滚一些不满足业务逻辑的修改，这没有问题，问题是我能在触发器中提交事务吗？

这个问题很小白，当也来测试一下

/*
测试中创建三种表,对表 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.
*/