不显示触发器中的Rollback Tran的出错信息

触发器中使用Rollback Tran来取消要执行的操作，但是这样会显示错误提示信息：

ALTER TRIGGER TestTrigger
ON TestTable
FOR  INSERT
AS
ROLLBACK TRAN

 

INSERT INTO TestTable VALUES ( 4 )

 

Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

 

为了在执行Insert操作的时候不会显示上述操作，可将FOR  INSERT改为INSTEAD OF  INSERT：

ALTER trigger TestTrigger
ON TestTable
INSTEAD OF  INSERT
AS
IF @@RowCount > 1
BEGIN
  -- ToDo: 做自定义处理
END

 

创建触发器的语法如下：

CREATE TRIGGER [owner.] trigger_name
ON [owner.] table_name
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
AS
[IF UPDATE (column_name)...]
[{AND | OR} UPDATE (column_name)...]
sql_statements}

 

INSTEAD OF triggers cancel the triggering action and perform a new function instead.

转载于:https://www.cnblogs.com/NewSunshineLife/archive/2010/05/10/1731710.html