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

作者蓉儿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 行受影响)
*/

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/04/24/4115964.aspx