if exists(select * from sysobjects where name = 'trig_setstate') drop trigger trig_setstate gocreate trigger trig_setstate on dbo.FittQuote for update as begin update a set state = 1 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid <>b.fittquoteid and b.state=2 and a.state=2 update a set state = 2 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=1 and a.state=1 update a set state = 2 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=0 and a.state=0 end
if exists(select * from sysobjects where name = 'trig_setstate') drop trigger trig_setstate gocreate trigger trig_setstate on dbo.FittQuote for update as begin update a set state = 1 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid <>b.fittquoteid and b.state=2 and a.state=2 update a set state = 2 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=1 and a.state=1 update a set state = 1 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=0 and a.state=0 end
在SELECT是不能触发的.
只有表里的数据发生INSERT、UPDATE、DELETE这些操作才能触发触发器。
可以参考一下这个:
http://blog.csdn.net/sdhdy/archive/2009/06/07/4249668.aspx
drop trigger trig_setstate
gocreate trigger trig_setstate
on dbo.FittQuote
for update
as
begin
update a set state = 1 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid <>b.fittquoteid and b.state=2 and a.state=2
update a set state = 2 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=1 and a.state=1
update a set state = 2 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=0 and a.state=0
end
drop trigger trig_setstate
gocreate trigger trig_setstate
on dbo.FittQuote
for update
as
begin
update a set state = 1 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid <>b.fittquoteid and b.state=2 and a.state=2
update a set state = 2 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=1 and a.state=1
update a set state = 1 from fittquote a join deleted b on a.fittbuyid=b.fittbuyid and a.fittquoteid =b.fittquoteid and b.state=0 and a.state=0
end