use a
gocreate trigger tr_a_insert
on a
for insert
asinsert b.dbo.b select * from inserted
gocreate trigger tr_a_delete
on a
for delete
asdelete x
from deleted d,b.dbo.b x
where x.keyfield =d.keyfield
go
create trigger tr_a_update
on a
for update
asupdate x
col1=i.col1,.....
from deleted d,b.dbo.b x ,inserted i
where x.keyfield =d.keyfield
and i.keyfield =d.keyfieldgo
gocreate trigger tr_a_insert
on a
for insert
asinsert b.dbo.b select * from inserted
gocreate trigger tr_a_delete
on a
for delete
asdelete x
from deleted d,b.dbo.b x
where x.keyfield =d.keyfield
go
create trigger tr_a_update
on a
for update
asupdate x
col1=i.col1,.....
from deleted d,b.dbo.b x ,inserted i
where x.keyfield =d.keyfield
and i.keyfield =d.keyfieldgo
as
if update()
update table set a=isnull(a,0)+b
from table ,(select id,sum(a) as b from inserted) a where table.id=a.id
insert into table ()
select * from insered a left join table b on a.id=b.id
where b.id is null
delete 操作产生deleteed 临时表(与删除记录同)UPDATE 操作产生
INSERTED临时表(与修改后记录同)
deleteed 临时表(与修改前记录同)
以上表可以按表使用(SELECT )
FOR INSERT
AS
insert db2..tabSlave select * from insertedCREATE TRIGGER 名2 ON dbo.tabMain
FOR update
AS
update db2..tabSlave set 列=aa.列 from inserted aa where aa.编号=db2..tabSlave.编号
CREATE TRIGGER 名3 ON dbo.tabMain
FOR delete
AS
delete db2..tabSlave where 编号 in (select 编号 from deleted)
(商品编号 varchar(20) not null PRIMARY KEY CLUSTERED ,
商品名称 varchar(20) UNIQUE NONCLUSTERED ,
数量 int
)
create table 次表
(商品编号 varchar(20) not null PRIMARY KEY CLUSTERED REFERENCES 主表 (编号) ON DELETE CASCADE ON UPDATE CASCADE ,
商品名称 varchar(20) ,
供应商 varchar(20)
)
update 主表的商品编号次表的商品编号也会更新
delete 主表的记录次表的商品编号相同的记录会删除