本人现在想写个更新用的触发器,功能如下:
当更新表A某条记录的非c1列时,将当前更新记录的c1列置为2,而更新c1列是不进行任何操作。
我写过一个,但问题就出在后边的限制条件上,当更新c1列时,触发器把c1列也更新为2了,起不到更新效果。希望知道的给指点下,谢谢!
--更新结帐表时处理DataDealState字段的触发器
if Exists(select name from sysobjects where name = 'trig_Updatecheckout' and xtype = 'TR')
drop Trigger trig_Updatecheckout
goCreate Trigger trig_Updatecheckout
On TCheckOut
for Update
As
begin
update a set DataDealState = 2 From TCheckOut a, inserted b Where a.CheckOutID = b.CheckOutID
end
go
当更新表A某条记录的非c1列时,将当前更新记录的c1列置为2,而更新c1列是不进行任何操作。
我写过一个,但问题就出在后边的限制条件上,当更新c1列时,触发器把c1列也更新为2了,起不到更新效果。希望知道的给指点下,谢谢!
--更新结帐表时处理DataDealState字段的触发器
if Exists(select name from sysobjects where name = 'trig_Updatecheckout' and xtype = 'TR')
drop Trigger trig_Updatecheckout
goCreate Trigger trig_Updatecheckout
On TCheckOut
for Update
As
begin
update a set DataDealState = 2 From TCheckOut a, inserted b Where a.CheckOutID = b.CheckOutID
end
go
On TCheckOut
for Update
As
begin
if Not Update(DataDealState)
update a set DataDealState = 2 From TCheckOut a, inserted b Where a.CheckOutID = b.CheckOutID
end
go
drop Trigger trig_Updatecheckout
go Create Trigger trig_Updatecheckout
On TCheckOut
for Update
As
begin
if update(c1) -- 更新c1就直接退出
return;
update a set DataDealState = 2 From TCheckOut a, inserted b Where a.CheckOutID = b.CheckOutID
end
go
set DataDealState = 2
From TCheckOut a, inserted b,deleted d
Where a.CheckOutID = b.CheckOutID and b.CheckOutID=d.CheckOutID
if Exists(select name from sysobjects where name = 'trig_Updatecheckout' and xtype = 'TR')
drop Trigger trig_Updatecheckout
go Create Trigger trig_Updatecheckout
On TCheckOut
for Update
As
begin update a
set DataDealState = 2
From TCheckOut a, inserted b
Where a.CheckOutID = b.CheckOutID and a.DataDealState = b.DataDealState
end
go
set DataDealState = 2
From TCheckOut a, inserted b,deleted d
Where a.CheckOutID = b.CheckOutID and b.CheckOutID=d.CheckOutID and b.DataDealState=d.DataDealState
还是用update()比较简洁,学习了。
create table TCheckOut(CheckOutID int,DataDealState int)
insert TCheckOut select 1,2 union select 2,4 union select 3,8
go
/*
CheckOutID DataDealState
----------- -------------
1 2
2 4
3 8
*/
Create Trigger trig_Updatecheckout
On TCheckOut
for Update
As
begin
if Not Update(DataDealState)
update a set DataDealState = 2 From TCheckOut a, inserted b Where a.CheckOutID = b.CheckOutID
end
go
--测试语句
update TCheckOut set CheckOutID=100 where CheckOutID=3
select * from TCheckOut
/*
CheckOutID DataDealState
----------- -------------
1 2
2 4
100 2
*/drop table TCheckOut