当触发如下操作的时候,进行相应操作。
1.当 update A set f1=v1 时候,进行如下操作:update B set f1=v1 where B.f2=(select A.f2 where A.f1=v1) 2.当 delete from A where f1=v1时候,进行如下操作:delete from B where B.f2=(select A.f2 where A.f1=v1)
1.当 update A set f1=v1 时候,进行如下操作:update B set f1=v1 where B.f2=(select A.f2 where A.f1=v1) 2.当 delete from A where f1=v1时候,进行如下操作:delete from B where B.f2=(select A.f2 where A.f1=v1)
where id = object_id(N'[dbo].[tri_clerk]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tri_clerk]
gocreate trigger tri_clerk on clerk_lst
for insert,delete/**,update**/ as
begin
declare @cl char(4);
declare @icnt int;
declare @dcnt int; select @icnt = count(*) from inserted;
select @dcnt = count(*) from deleted; if (@icnt > 0)
begin
if (@dcnt = 0)--插入数据时触发
begin
select @cl = clerk_id from inserted;
insert into mySHOPSHStock.dbo.userfonshow(mrid,userid) values(1,@cl);
insert into mySHOPSHStock.dbo.userfonshow(mrid,userid) values(2,@cl);
insert into mySHOPSHStock.dbo.userfonshow(mrid,userid) values(3,@cl);
insert into mySHOPSHStock.dbo.userfonshow(mrid,userid) values(4,@cl);
end
/**else if (@dcnt > 0)--更新数据时触发
begin
--此trigger不要用到
end**/
end
else if (@dcnt > 0)--删除数据时触发
begin
select @cl=clerk_id from deleted;
delete from mySHOPSHStock.dbo.userfonshow where userid = @cl;
end
end
go改改应该可以满足你的需求
这个可以在存储过程里一起封装,就是平时我们的业务一样,当做了什么后,就做什么。
触发器里面是可以,但是感觉浪费太大了。因为你只有当f1=v1时的操作才执行后面的。
FOR UPDATE,DELETE
AS
BEGIN
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) -- update的时候
update B set f1=v1 where B.f2=(select f2 from inserted where f1=v1)
IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) --delete的时候
delete from B where B.f2= (select f2 from deleted where f1=v1)
END
create trigger [改成你的触发器名]
on dbo.[A]
for update,delete
as
begin
set nocount on;
--只在update、且f1的值变更时才触发
if exists(select 1 from inserted i inner join deleted d on i.[改成你的列名]=d.[改成你的列名] where i.f1<>d.f1)
begin
--需考虑(select A.f2 where A.f1=v1)是否只返回一个值,否则会提示错误。如果返回多个值,可以改成B.f2 in (select A.f2 where A.f1=v1)
update B set f1=v1 where B.f2=(select A.f2 where A.f1=v1)
end
--删除时触发
if not exists(select 1 from inserted) and exists(select 1 from deleted)
begin
delete from B where B.f2=(select A.f2 where A.f1=v1)
end
set nocount off;
end
delete from B where B.f2=(select A.f2 where A.f1=v1)
是复制下来的,楼主写的时候需要改一下。