触发器中调用存储过程,事务的使用

作者蓉儿szx1999

1.set xact_abort off
2.设置保存点

IF OBJECT_ID('TA')IS NOT NULL DROP TABLE TA
GO
CREATE TABLE TA( ID INT IDENTITY,性別 NVARCHAR(5) CHECK(性別 IN(N'男',N'女')))
IF OBJECT_ID('PRO_TEST')IS NOT NULL DROP PROC PRO_TEST

GO
CREATE PROC PRO_TEST
@SEX NVARCHAR(5)
AS
INSERT TA SELECT @SEX
GO
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT ,[NAME] VARCHAR(10))
INSERT TB SELECT 1,'張三'
IF OBJECT_ID('TRI_TEST')IS NOT NULL DROP TRIGGER TRI_TEST
GO
CREATE TRIGGER TRI_TEST ON TB
FOR UPDATE
AS
set xact_abort off
IF UPDATE(ID)
save tran procTran
EXEC PRO_TEST 'TEST'
IF(@@error <> 0)
ROLLBACK TRAN procTran
ELSE
COMMIT TRAN procTran

set xact_abort on
GO

UPDATE TB SET ID=888 WHERE ID=1
select * from tb
/*
ID NAME
----------- ----------
888 張三

(1 行受影响)
*/