请问,我有两个表,
表1(ID,A1,B1,C1,D1,E1,F1),表2(ID,A1,B1,D1)
表1中F1为Bit型(默认值为1),假设我表1中有若干条记录,F1都为1。
现在我要求的时,如何根据表1中的F1值由1改为0时(即条件是表1.F1=0),将修改前的记录存入表2中(注意:仅存入部分字段且是表1修改前的记录)。
当然这个确发器也要包含删除表1的记录同时也删除表2中相同的记录,但插入一条新记录时对表2不作任何改动。
请各位高人指点,怎么实现?
表1(ID,A1,B1,C1,D1,E1,F1),表2(ID,A1,B1,D1)
表1中F1为Bit型(默认值为1),假设我表1中有若干条记录,F1都为1。
现在我要求的时,如何根据表1中的F1值由1改为0时(即条件是表1.F1=0),将修改前的记录存入表2中(注意:仅存入部分字段且是表1修改前的记录)。
当然这个确发器也要包含删除表1的记录同时也删除表2中相同的记录,但插入一条新记录时对表2不作任何改动。
请各位高人指点,怎么实现?
AS
update 表2
set A1 = 表1,A1 , B1 = 表1.B1 , D1 = 表1.D1
from 表2,表1
where 表2.ID = 表.ID
GO
谢谢了
on 表1
for update
as
set nocount on
if update(F1)
begin
--删除表2
delete c from 表2 c
join deleted a
on c.ID = a.ID
join inserted b
on c.ID = b.ID
where a.F1=0 and b.F1=1
--插入表2
insert 表2
select a.ID,a.A1,a.B1,a.D1
from deleted a
join inserted b
on a.ID = b.ID
where a.F1=0 and b.F1=1
--删除表1
delete c from 表1 c
join deleted a
on c.ID = a.ID
join inserted b
on c.ID = b.ID
where a.F1=0 and b.F1=1end
set nocount off
go
AS
Delete from 表2 where id not in (select id from 表1)
GO
AS
update 表2
set A1 = 表1,A1 , B1 = 表1.B1 , D1 = 表1.D1
from 表2,表1
where 表2.ID = 表.ID
GOCREATE TRIGGER 表1_delete ON 表1 FOR Delete
AS
Delete from 表2 where id not in (select id from 表1)
GO
AS
update 表2
set A1 = 表1,A1 , B1 = 表1.B1 , D1 = 表1.D1
from 表2,表1
where 表2.ID = 表1.ID and 表1.F1 = 0
GOCREATE TRIGGER 表1_delete ON 表1 FOR Delete
AS
Delete from 表2 where id not in (select id from 表1)
GO
after update
as
insert into 表2
select id,a1,b1,d1 from deleted where f1=1
on 表1
for update
as
begin
insert into 表2 (ID,A1,B1,D1) values (select D,A1,B1,D1 from inserted where f1=1)
end
go
if update(F1)
insert 表2 select a.id,a.A1,a.B1,a.D1 from deleted a,inserted b where a.id=b.id and a.F1=1 and b.F1=0
create trigger tri_b on 表1 after delete as
delete 表2 where id in(select id from deleted)