两个表a与b,结构相同,
shopcode char(2) not null,
shead bit not null,
slocal bit not null,希望做一个触发器.要求为:
(1)当a表中有新记录插入时,如果slocal=1的话,那么把此记录插入b表。如果slocal=0的话,此记录不插入b表。
(2)当a表中修改某条记录时,如果slocal=1的话,那么把此记录更新到b表。如果slocal=0的话,跳过。
shopcode char(2) not null,
shead bit not null,
slocal bit not null,希望做一个触发器.要求为:
(1)当a表中有新记录插入时,如果slocal=1的话,那么把此记录插入b表。如果slocal=0的话,此记录不插入b表。
(2)当a表中修改某条记录时,如果slocal=1的话,那么把此记录更新到b表。如果slocal=0的话,跳过。
on a
for insert
insert into b select * from inserted where slocal=1
create trigger tt on a
for insert
as
begin
insert into b
select * from inserted where shead=1
end
on a
for insert
as
insert into b select * from inserted where slocal=1
for update
as
begin
delete from b where shopcode in (select shopcode from deleted where inserted=1)
insert into b
select * from inserted where shead=1
end
create trigger tri_a on a for insert,update
as
if exists(select 1 from inserted where slocal=1)
begin
if exists(select 1 from b where shopcode=(select shopcode from inserted))
begin
update b set shead=a.shead,slocal=a.slocal from b,inserted a
where a.shopcode=b.shopcode
end
else
begin
insert into b select * from inserted
end
end
on a
after insert
as
insert into b
select * from a where slocal=1
gocreate trigger tri_test2
on a
after update
as
if exists(select * from inserted where slocal=1)
update b
set b.xx=i.xx
from b,deleted d,inserted i
where b.xx=d.xx
go
for update
as
begin
delete from b where shopcode in (select shopcode from deleted where inserted=1)
insert into b
select * from inserted where shopcode in (select shopcode from deleted where inserted=1)
end
as
begin
if exists(select 1 from inserted where slocal=1)
begin
delete from b where id = (select id from inserted)
insert into b select * from inserted
end
end
把ID改为shopcodecreate trigger tt on a for insert,update
as
begin
if exists(select 1 from inserted where slocal=1)
begin
delete from b where shopcode = (select shopcode from inserted)
insert into b select * from inserted
end
end
create table dep
(
id int identity(1,1) not null,
code int,
depName nvarchar(50)
)create table emp
(
id int identity(1,1) not null,
empName char(10),
depName nvarchar(50)
)insert dep select 11,'AA'insert dep select 22,'BB'insert dep select 33,'CC'
insert emp select 'aa','AA'insert emp select 'bb','BB'insert emp select 'cc','CC'
create trigger tri_dep_name
on dep
for update
as
begin
declare @DepName nvarchar(50),@newDepName nvarchar(50)
select @DepName=depName from deleted
select @newDepName=depName from inserted
update emp set depName=@newDepName where depName=@DepName
end
select * from dep
select * from emp