日期:2014-05-17 浏览次数:20410 次
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 主键=@Key
select @mainID
select * from #temp
go
--创建主表
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
*/