create trigger on a for update as update b set y=inserted.x where y=deleted.x
create trigger tri_test on a for update as if update(x) update b set y=a.x from inserted a where a.id=b.id
create trigger Update_b on a for update ,insert ,delete as begin if not exists(select 1 from deleted)--a表进行insert操作 insert into b(y) select x from insertedif not exists(select 1 from inserted)--a表进行delete操作 delete from b where y in (select x from deleted)if exists(select 1 from inserted) and exists(select 1 from deleted)--a表进行update操作 update b set b.y=i.x from b,inserted i,deleted d where b.y=d.y end
if exists(select 1 from inserted) and exists(select 1 from deleted)--a表进行update操作 update b set b.y=i.x from b,inserted i,deleted d where b.y=d.y end ~~~~~~~~~~~~~~~~~~~~~~ d和i是哪儿来的呢?
回chuifengde(树上的鸟儿) update b set y=a.x from inserted a where a.id=b.id ~~~~~~~~~~~~~~~~ 触发器里set y=a.x可以这样写?好像不认a.x吧~~
--两个表A,B --B表中的y列的值都是从A表中x列里取的 我想让A.x的值变化时对应的B.y的那些值跟着变化。如何写?谢谢 create table a ( uid int, uname varchar(50) ) insert into a values(1,'taiji') insert into a values(2,'baguai') insert into a values(3,'tianlong') insert into a values(4,'cao') insert into a values(5,'ddd') insert into a values(6,'yun') insert into a values(7,'lijdjjie') select * from adrop table acreate table b ( bid int identity, uname varchar(50) )insert into b(uname) select uname from acreate trigger DoOne on a for update as update b set uname = (select uname from inserted ) where uname=(select uname from deleted) drop trigger DoOneupdate a set uname = 'aaa' where uid=1
create trigger DoOne on a for update as update b set uname = (select uname from inserted ) where uname=(select uname from deleted) 这是重点
for update
as
update b set y=inserted.x where y=deleted.x
for update
as
if update(x)
update b set y=a.x from inserted a where a.id=b.id
for update ,insert ,delete
as
begin
if not exists(select 1 from deleted)--a表进行insert操作
insert into b(y) select x from insertedif not exists(select 1 from inserted)--a表进行delete操作
delete from b where y in (select x from deleted)if exists(select 1 from inserted) and exists(select 1 from deleted)--a表进行update操作
update b set b.y=i.x from b,inserted i,deleted d
where b.y=d.y
end
update b set b.y=i.x from b,inserted i,deleted d
where b.y=d.y
end
~~~~~~~~~~~~~~~~~~~~~~
d和i是哪儿来的呢?
update b set y=a.x from inserted a where a.id=b.id
~~~~~~~~~~~~~~~~
触发器里set y=a.x可以这样写?好像不认a.x吧~~
--B表中的y列的值都是从A表中x列里取的 我想让A.x的值变化时对应的B.y的那些值跟着变化。如何写?谢谢
create table a
(
uid int,
uname varchar(50)
)
insert into a values(1,'taiji')
insert into a values(2,'baguai')
insert into a values(3,'tianlong')
insert into a values(4,'cao')
insert into a values(5,'ddd')
insert into a values(6,'yun')
insert into a values(7,'lijdjjie')
select * from adrop table acreate table b
(
bid int identity,
uname varchar(50)
)insert into b(uname) select uname from acreate trigger DoOne
on a for update
as
update b set uname = (select uname from inserted ) where uname=(select uname from deleted) drop trigger DoOneupdate a set uname = 'aaa' where uid=1
on a for update
as
update b set uname = (select uname from inserted ) where uname=(select uname from deleted)
这是重点