本贴并非问题。只是为了回答“新手提问有关SQL触发器的问题 [已结贴,结贴人:vickiyan]” 这个哥哥我在回贴,还没有打完字,他老人家就结贴了,有些问题没有和他说清楚呢
[code=SQL]
CREATE TRIGGER TR_SendB ON dbo.SendB
FOR UPDATE
NOT FOR REPLICATION --这条记得加上啊,
AS
DECLARE @ID INT
DECLARE @MOBB VARCHAR(50)
DECLARE @State
SET @ID =(SELECT ID FROM INSERTED)
SET @MOBB=(SELECT MOBB FROM INSERTED)
SET @State=(SELECT State FROM INSERTED)
PRINT @ID
PRINT @MOBB
PRINT @StateIF UPDATE (State)
BEGIN
UPDATE AcceptB SET Reply=@State WHERE Accept.ID=@ID AND Accept.MOBB=@MoBB AND Reply<>@State
END
这样写的好处是在UPDATE AcceptA时不用关联到INSERTED临时表,不会出现超时等无数据反回的情况。另外,请不要使用
SQL codeUPDATE SendB SET Reply=1 OR
UPDATE SendB SET Reply=0
[code]
如果要用的话,请将我上面的触发器语句用游标来处理。不知道楼主是否要用到批处理SendB呢?上面的回答没有一个涉及了这个问题的。一次更新一条是没有问题的,但一次更新很多条就会出问题哦。
如果需要指更新,要使用带游标的触发器,带游标的触发器写法如下:
[code=SQL]
CREATE TRIGGER TR_SendB ON dbo.SendB
FOR UPDATE
NOT FOR REPLICATION
AS DECLARE @ID INT
DECLARE @MOBB VARCHAR(50)
DECLARE @State INT
/*
SET @ID =(SELECT ID FROM INSERTED)
SET @MOBB=(SELECT MOBB FROM INSERTED)
SET @State=(SELECT State FROM INSERTED)
PRINT @ID
PRINT @MOBB
PRINT @State
*/
SET NOCOUNT ONIF UPDATE (State)DECLARE GetCur CURSOR FOR
SELECT ID,MOBB,State
FROM INSERTED
GROUP BY ID,BOMM,State
--FOR READ ONLY
OPEN GetCur
FETCH NEXT FROM GetCur INTO (@ID,@MOBB,@State)WHILE @@FETCH_STATUS=0
BEGIN
UPDATE AcceptA SET Reply=@State WHERE Accept.ID=@ID AND Accept.MOBB=@MoBB AND Reply<>@State
--应该是不能更新Reply=@State的数据吧?
FETCH NEXT FROM GetCur INTO (@ID,@MOBB,@State)
EndCLOSE GetCur
DEALLOCATE GetCurSET NOCOUNT OFF
GO[code]IF UPDATE (State)应该放在哪儿我不记得了(在定义游标前还是游标后呢?),我现在没有可操作的数据库进行测试。你自己先测试一下行不行?
[code=SQL]
CREATE TRIGGER TR_SendB ON dbo.SendB
FOR UPDATE
NOT FOR REPLICATION --这条记得加上啊,
AS
DECLARE @ID INT
DECLARE @MOBB VARCHAR(50)
DECLARE @State
SET @ID =(SELECT ID FROM INSERTED)
SET @MOBB=(SELECT MOBB FROM INSERTED)
SET @State=(SELECT State FROM INSERTED)
PRINT @ID
PRINT @MOBB
PRINT @StateIF UPDATE (State)
BEGIN
UPDATE AcceptB SET Reply=@State WHERE Accept.ID=@ID AND Accept.MOBB=@MoBB AND Reply<>@State
END
这样写的好处是在UPDATE AcceptA时不用关联到INSERTED临时表,不会出现超时等无数据反回的情况。另外,请不要使用
SQL codeUPDATE SendB SET Reply=1 OR
UPDATE SendB SET Reply=0
[code]
如果要用的话,请将我上面的触发器语句用游标来处理。不知道楼主是否要用到批处理SendB呢?上面的回答没有一个涉及了这个问题的。一次更新一条是没有问题的,但一次更新很多条就会出问题哦。
如果需要指更新,要使用带游标的触发器,带游标的触发器写法如下:
[code=SQL]
CREATE TRIGGER TR_SendB ON dbo.SendB
FOR UPDATE
NOT FOR REPLICATION
AS DECLARE @ID INT
DECLARE @MOBB VARCHAR(50)
DECLARE @State INT
/*
SET @ID =(SELECT ID FROM INSERTED)
SET @MOBB=(SELECT MOBB FROM INSERTED)
SET @State=(SELECT State FROM INSERTED)
PRINT @ID
PRINT @MOBB
PRINT @State
*/
SET NOCOUNT ONIF UPDATE (State)DECLARE GetCur CURSOR FOR
SELECT ID,MOBB,State
FROM INSERTED
GROUP BY ID,BOMM,State
--FOR READ ONLY
OPEN GetCur
FETCH NEXT FROM GetCur INTO (@ID,@MOBB,@State)WHILE @@FETCH_STATUS=0
BEGIN
UPDATE AcceptA SET Reply=@State WHERE Accept.ID=@ID AND Accept.MOBB=@MoBB AND Reply<>@State
--应该是不能更新Reply=@State的数据吧?
FETCH NEXT FROM GetCur INTO (@ID,@MOBB,@State)
EndCLOSE GetCur
DEALLOCATE GetCurSET NOCOUNT OFF
GO[code]IF UPDATE (State)应该放在哪儿我不记得了(在定义游标前还是游标后呢?),我现在没有可操作的数据库进行测试。你自己先测试一下行不行?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货