两个表a与b,结构相同,
shopcode char(2) not null,
shead bit not null,
slocal bit not null, 希望做一个触发器.要求为:
(1)当a表中有新记录插入时,如果slocal=1的话,那么把slocal=0,其它字段值不变,将此记录插入b表。如果slocal=0的话,此记录不插入b表。
(2)当a表中修改某条记录时,如果slocal=1的话,那么把slocal=0,其它字段为修改后的值,将此记录更新到b表。如果slocal=0的话,跳过。
shopcode char(2) not null,
shead bit not null,
slocal bit not null, 希望做一个触发器.要求为:
(1)当a表中有新记录插入时,如果slocal=1的话,那么把slocal=0,其它字段值不变,将此记录插入b表。如果slocal=0的话,此记录不插入b表。
(2)当a表中修改某条记录时,如果slocal=1的话,那么把slocal=0,其它字段为修改后的值,将此记录更新到b表。如果slocal=0的话,跳过。
create trigger tri_a on a for insert,update
as
if exists(select 1 from inserted where slocal=1)
begin
delete bwhere shopcode=(select shopcode from inserted))
insert into b select shopcode,shead,0 from inserted
end
create trigger tri_a on a for insert,update
as
if exists(select 1 from inserted where slocal=1)
begin
delete b where shopcode=(select shopcode from inserted))
insert into b select shopcode,shead,0 from inserted
end
for insert, update
as
begin
delete from b where shopcode in (select shopcode from deleted where inserted=1)
insert into b
select * from inserted where shopcode not in (select shopcode from deleted where inserted=0)
end
sql基础知识 下载1次 评论:0条
你下了肯定不后悔。这里包括视图,触发器,索引,以及sql的基本知识。很不错的资料。