有没有什么办法,可以同时取到两个表中被删除记录的值? 实在是没有思路啊,能指点指点么 --->使用存储 过程吧 create proc proc_del @Key varchar(50) as set nocount on declare @mainID int ,@fkeyID int--先查询 select @mainID=ID from 主表 where 主键=@Key select ID as fkeyID into #temp from 外键表 where fkey=@mainID--再删除 delete from 外键表 where fkey=@mainID delete from 主表 where 主键=@Keyselect @mainID select * from #tempgo
我做了一个实验,通过2005版本及以后版本提供的output子句,来保存删除的记录,你看看是不是这样: --创建主表 create table t1(id int primary key,v varchar(10))--创建附表,级联删除 create table t2 ( idd int, id int foreign key references t1(id) on delete cascade, vv varchar(20) )insert into t1 select 1,'a' union all select 2,'b' insert into t2 select 1,1,'www' union all select 1,2,'csdn' --创建存储删除的t1表的字段 create table temp_t1_delete(id int,v varchar(10))--创建存储删除的t2表的字段 create table temp_t2_delete(id int,vv varchar(20)) go--创建表t2的delete触发器 create trigger dbo.trigger_t2_delete on dbo.t2 for delete as begin insert into temp_t2_delete(id,vv) select id,vv from deleted end go --删除主表记录,自动把删除的主表记录,保存在temp_t1_deletei表中 delete from t1 output deleted.id, --引用所有字段deleted.* deleted.v into temp_t1_delete where id = 1 --查询已删除的记录 select * from temp_t1_delete t1 left join temp_t2_delete t2 on t1.id = t2.id /* id v id vv 1 a 1 www */
--->使用存储 过程吧
create proc proc_del
@Key varchar(50)
as
set nocount on
declare @mainID int ,@fkeyID int--先查询
select @mainID=ID from 主表 where 主键=@Key
select ID as fkeyID
into #temp
from 外键表
where fkey=@mainID--再删除
delete from 外键表 where fkey=@mainID
delete from 主表 where 主键=@Keyselect @mainID
select * from #tempgo
--创建主表
create table t1(id int primary key,v varchar(10))--创建附表,级联删除
create table t2
(
idd int,
id int foreign key references t1(id) on delete cascade,
vv varchar(20)
)insert into t1
select 1,'a' union all
select 2,'b' insert into t2
select 1,1,'www' union all
select 1,2,'csdn'
--创建存储删除的t1表的字段
create table temp_t1_delete(id int,v varchar(10))--创建存储删除的t2表的字段
create table temp_t2_delete(id int,vv varchar(20))
go--创建表t2的delete触发器
create trigger dbo.trigger_t2_delete
on dbo.t2
for delete
as
begin
insert into temp_t2_delete(id,vv)
select id,vv
from deleted
end
go
--删除主表记录,自动把删除的主表记录,保存在temp_t1_deletei表中
delete from t1
output deleted.id, --引用所有字段deleted.*
deleted.v into temp_t1_delete
where id = 1 --查询已删除的记录
select *
from temp_t1_delete t1
left join temp_t2_delete t2
on t1.id = t2.id
/*
id v id vv
1 a 1 www
*/