trainTable(ID char(12) ,name char(12)) --ID为主键。
stateTable(ID char(12),state int)—— ID为主键。
一下是在trainTable上的触发器
————————————————————————
CREATE TRIGGER trainTigger ON dbo.trainTable
FOR INSERT,UPDATE,DELETE
AS
declare @i char
declare @k char
select @i=ID from inserted
select @k=ID from deleted
if exists(select * from inserted) and not exists(select * from deleted)
insert into stateTable(ID,state) values(@i,0)
if exists(select * from inserted) and exists(select * from deleted)
update stateTable set state=0 where ID=@k
if not exists(select * from inserted) and exists(select * from deleted)
delete from stateTable where ID=@k
---------------------------------------------------------------
为何我在trainTable表上执行insert update delete时,trainTigger触发器没有生效。
请大师指点。谢谢。
stateTable(ID char(12),state int)—— ID为主键。
一下是在trainTable上的触发器
————————————————————————
CREATE TRIGGER trainTigger ON dbo.trainTable
FOR INSERT,UPDATE,DELETE
AS
declare @i char
declare @k char
select @i=ID from inserted
select @k=ID from deleted
if exists(select * from inserted) and not exists(select * from deleted)
insert into stateTable(ID,state) values(@i,0)
if exists(select * from inserted) and exists(select * from deleted)
update stateTable set state=0 where ID=@k
if not exists(select * from inserted) and exists(select * from deleted)
delete from stateTable where ID=@k
---------------------------------------------------------------
为何我在trainTable表上执行insert update delete时,trainTigger触发器没有生效。
请大师指点。谢谢。
declare @k char
应该改为declare @i char(12)
declare @k char(12)
if exists(select * from inserted) and exists(select * from deleted)
update stateTable set state=0 where ID=@k
让人费解,update操作应该有增加也有减少其次,你的写法在一次插入、修改、删除多条记录的情况是不正常的我估计你需要的触发器CREATE TRIGGER trainTigger ON dbo.trainTable
FOR INSERT,UPDATE,DELETE
AS delete from stateTable where ID in (select id from deleted)
insert into stateTable(ID,state) select id,0 from inserted
go