有可能,如果没有外建设置Create trigger test on base for delete as insert into backup (name,class) select i.name,i.class from info i,deleted d where d.name=i.name --delete info where name in (select name from deleted) delete i from info i,deleted d where d.name=i.name go如果有外键(必须先删除info),就用instead of触发器Create trigger test on base instead of delete as insert into backup (name,class) select i.name,i.class from info i,deleted d where d.name=i.name delete i from info i,deleted d where d.name=i.name delete b from base b,deleted d where d.name=b.name go
Create trigger test on base for delete as delete from info where name=(select name from deleted) insert into backup (name) select name from info where name=(select name from deleted) go
deleted是指向 base表的所以会出现楼主的问题.Create trigger test on base for delete as insert into backup select b.* from deleted a,info b where a.name=b.name delete from info where name=(select name from deleted)
這個不適合用嵌套因為你要插入的記錄就是info表要刪除的記錄﹐所以你應該先把into表的記錄拿出來﹐然后同志對它的數據刪除Create trigger test on base for delete as insert into backup (name) select name from info where name in(select name from deleted) delete from info where name=(select name from deleted)
create trigger trginsert on base for delete as declare @x char(8) declare @y char(8) select @x=b.[name],@y=b.[class] from deleted a left outer join info b on a.[name]=b.[name] insert backup1([name],[class]) values(@x,@y)
Create trigger test on base for delete as delete from info where name=(select name from deleted) insert into backup (name,class) select name,calss from info where name=(select name from deleted) go
for delete
as
insert into backup (name,class) select i.name,i.class from info i,deleted d
where d.name=i.name
--delete info where name in (select name from deleted)
delete i
from info i,deleted d
where d.name=i.name
go如果有外键(必须先删除info),就用instead of触发器Create trigger test on base
instead of delete
as
insert into backup (name,class) select i.name,i.class from info i,deleted d
where d.name=i.name delete i
from info i,deleted d
where d.name=i.name delete b
from base b,deleted d
where d.name=b.name
go
for delete
as
delete from info where name=(select name from deleted)
insert into backup (name) select name from info where name=(select name from deleted)
go
for delete
as
insert into backup select b.* from deleted a,info b where a.name=b.name
delete from info where name=(select name from deleted)
go
一个事务里,先后次序没关系吧?先将张三数据插入backup,再将info表中张三的数据清空
on base
for delete
as
insert into backup (name) select name from info where name in(select name from deleted) delete from info where name=(select name from deleted)
go
這個不適合用嵌套因為你要插入的記錄就是info表要刪除的記錄﹐所以你應該先把into表的記錄拿出來﹐然后同志對它的數據刪除
用嵌套是这样的,在base表中建立触发器,当删除时先删除info表中对应项,然后再建触发器,建在info表中,当info表删除时执行insert into,这样我做下来是成功的,这样似乎也比现在说的这个要更适合初学者理解!非常感谢大家的讨论,我在测试中...
on base
for delete
as
declare @x char(8)
declare @y char(8)
select @x=b.[name],@y=b.[class] from deleted a left outer join info b on a.[name]=b.[name]
insert backup1([name],[class])
values(@x,@y)
for delete
as
delete from info where name=(select name from deleted)
insert into backup (name,class) select name,calss from info where name=(select name from deleted)
go