SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOALTER TRIGGER [THSTATE] ON dbo.JXNF_CGTHHEAD FOR UPDATE AS IF UPDATE (STATE) BEGIN DECLARE @STATE NVARCHAR(20) DECLARE @THID INT DECLARE @DHID INT SELECT @STATE = STATE,@THID=THID,@DHID=DHID FROM INSERTED IF @STATE = '完成' BEGIN DECLARE @CGID INT DECLARE @PID INT DECLARE @THNUM FLOAT DECLARE CUR CURSOR FOR SELECT CGID,PRODUCTID,THNUM FROM JXNF_CGTHBODY OPEN CUR FETCH FROM CUR INTO @CGID,@PID,@THNUM WHILE @@FETCH_STATUS = 0 BEGIN UPDATE JXNF_CGBILLBODY SET DHNUM=DHNUM-@THNUM WHERE HEADID=@CGID AND PRODUCTID=@PID UPDATE JXNF_CGDHITEM SET THNUM=THNUM+@THNUM WHERE DHID=@DHID AND CGID=@CGID AND PRODUCTID=@PID UPDATE JXNF_PARIVER SET THNUM=THNUM+@THNUM WHERE HID=@DHID AND PRODUCTID=@PID FETCH FROM CUR INTO @CGID,@PID,@THNUM END CLOSE CUR DEALLOCATE CUR END END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
1、 如果你的代码完善,可以做到按你的要求来处理,就是说,可以做到控制是否继续执行和控制是否回滚。
2、如果你的代码没做处理,那就是按照sql server默认的设置,应该是出错不会继续执行,回滚整个事务,整个事务的意思是包括触发触发器的事务一起回滚。
GO
SET ANSI_NULLS ON
GOALTER TRIGGER [THSTATE] ON dbo.JXNF_CGTHHEAD
FOR UPDATE
AS
IF UPDATE (STATE)
BEGIN
DECLARE @STATE NVARCHAR(20)
DECLARE @THID INT
DECLARE @DHID INT
SELECT @STATE = STATE,@THID=THID,@DHID=DHID FROM INSERTED
IF @STATE = '完成'
BEGIN
DECLARE @CGID INT
DECLARE @PID INT
DECLARE @THNUM FLOAT
DECLARE CUR CURSOR FOR
SELECT CGID,PRODUCTID,THNUM FROM JXNF_CGTHBODY
OPEN CUR
FETCH FROM CUR INTO @CGID,@PID,@THNUM
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE JXNF_CGBILLBODY SET DHNUM=DHNUM-@THNUM WHERE HEADID=@CGID AND PRODUCTID=@PID
UPDATE JXNF_CGDHITEM SET THNUM=THNUM+@THNUM WHERE DHID=@DHID AND CGID=@CGID AND PRODUCTID=@PID
UPDATE JXNF_PARIVER SET THNUM=THNUM+@THNUM WHERE HID=@DHID AND PRODUCTID=@PID
FETCH FROM CUR INTO @CGID,@PID,@THNUM
END
CLOSE CUR
DEALLOCATE CUR END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
另一个语句正确 结果是另一个语句执行有效 值发生了改变这样测试得到的结果正确吗
是否回滚与SET XACT_ABORT 以及错误级别相关。
当SET XACT_ABORT OFF(缺省)时,只有发生严重错误时才会回滚。
当SET XACT_ABORT ON时,发生错误时会回滚。
如果里面有print 你会看到做到哪一步出错全部回滚,不插入任何数据除非你做了另外的设置。
一个执行语句(UPDATE/INSERT/DELETE)和他所有的触发器都处于同一个事务当中。
http://blog.csdn.net/misterliwei/archive/2009/12/30/5106623.aspx