实例说明 sql server 多表级联删除的两种方式

ts2009

　　--实例说明 sql server 多表级联删除的两种方式
　　
　　--一、触发器方式，级联删除
if object_id('tableC') is not null drop table tableC
go
if object_id('tableB') is not null drop table tableB
go
if object_id('tableA') is not null drop table tableA
go
create table tableA (Aid varchar(10) primary key,Aname varchar(20))
insert tableA select 'A1','公司1'
go
create table tableB (Bid varchar(10) primary key,Bname varchar(20),Aid varchar(10) references tableA(Aid) )
insert tableB
select 'B1','部门1','A1' union all
select 'B2','部门2','A1'
go
create table tableC (Cid varchar(10) primary key,Cname varchar(20),Bid varchar(10) references tableB(Bid))
insert tableC
select 'C1','人员1','B1' union all
select 'C2','人员2','B1' union all
select 'C3','人员3','B2' union all
select 'C4','人员4','B2'
go
--创建触发器
if object_id('trd_A') is not null drop trigger trd_A
go
create trigger trd_A
on tableA
instead of delete
as
begin
set nocount on
delete from tableB
where Aid in (select Aid from deleted)
delete from tableA where Aid in (select Aid from deleted)
set nocount off
end
go
if object_id('trd_B') is not null drop trigger trd_B
go
create trigger trd_B
on tableB
instead of delete
as
begin
set nocount on
delete from tableC
where Bid in (select Bid from deleted)
delete from tableB where Bid in (select Bid from deleted)
set nocount off
end
go
--测试
delete from tableA
select * from tableA
select * from tableB
select * from tableC
--结果
/*
Aid Aname
---------- --------------------
　　(0 行受影响)
　　Bid Bname Aid
---------- -------------------- ----------
　　(0 行受影响)
　　Cid Cname Bid
---------- -------------------- ----------
　　(0 行受影响)
　　*/
　　
--二、设置外键级联删除方式，级联删除
if object_id('tableC') is not null drop table tableC
go
if object_id('tableB') is not null drop table tableB
go
if object_id('tableA') is not null drop table tableA
go
create table tableA (Aid varchar(10) primary key,Aname varchar(20))
insert tableA select 'A1','公司1'
go
create table tableB (Bid varchar(10) primary key,Bname varchar(20),Aid varchar(10) references tableA(Aid) on delete cascade )
insert tableB
select 'B1','部门1','A1' union all
select 'B2','部门2','A1'
go
create table tableC (Cid varchar(10) primary key,Cname varchar(20),Bid varchar(10) references tableB(Bid) on delete cascade )
insert tableC
select 'C1','人员1','B1' union all
select 'C2','人员2','B1' union all
select 'C3','人员3','B2' union all
select 'C4','人员4','B2'
go
--测试
delete from tableA
select * from tableA
select * from tableB
select * from tableC
--结果
/*
Aid Aname
---------- --------------------
　　(0 行受影响)
　　Bid Bname Aid
---------- -------------------- ----------
　　(0 行受影响)
　　Cid Cname Bid
---------- -------------------- ----------
　　(0 行受影响)
　　*/
　　
　　
　　
　　带日志：
　　if object_id('so') is not null drop table so
go
CREATE TABLE [SO](
[SO_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,--订单号
[so_cjsj] [datetime] NULL DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
[SO_SOID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
if object_id('trd_so') is not null drop trigger trd_so
go
create trigger trd_so
on so
instead of delete
as
begin
delete from [SOMX] where [SOMX_SOID] in(select [SO_SOID] from deleted)
delete from [SO] where [SO_SOID] in(select [SO_SOID] from deleted)
insert [DeletedHistory] select [SO_SOID],'SO',getdate() from deleted
end
go
if object_id('somx') is not null drop table somx
go
CREATE TABLE [SOMX](
[SOMX_SOID] [char](10) COLLATE Chinese_PRC_CI_AS NOT NULL,---订单号
[SOMX_SOXH] [float] NOT NULL, ---订单序号
[u_somx_whsj] [datetime] NULL, ---维护时间
CONSTRAINT [PK_jserp.somx] PRIMARY KEY NONCLUSTERED
(
[SOMX_SOID] ASC,
[SOMX_SOXH] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
if object_id('trd_SOMX') is not null drop trigger trd_SOMX
go
create trigger trd_SOMX
on SOMX
for delete
as
begin
insert [DeletedHistory] select rtrim([SOMX_SOID])+'+'+ltrim([SOMX_SOXH]),'SOMX',getdate() from deleted
end
go
　　if object_id('DeletedHistory') is not null drop table DeletedHistory
go
CREATE TABLE [DeletedHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeleteID] [nchar](40) COLLATE Chinese_PRC_CI_AS NOT NULL, ---删除记录的主键，联合主键的采用“+”连接
[TableName] [nchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL, ---删除记录所在表 表名
[DeleteDate] [datetime] NOT NULL CONSTRAINT [DF_DeletedHistory_DeleteDate] DEFAULT (CONVERT([datetime],CONVERT([char](100),getdate(),(120)),(0))),
CONSTRAINT [PK_DeletedHistory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
--测试
insert so select 'A001',getdate() union select 'A002',getdate()
insert [SOMX]
select 'A001','1',getdate() union
select 'A001','2',getdate() union
select 'A001','3',getdate() union
select 'A002','1',getdate() union
select 'A002','2',getdate()
go
delete [SOMX] where [SOMX_SOID]='A001' and [SOMX_SOXH]=1
select * from [DeletedHistory]
go
delete [SO] where [SO_SOID]='A002'
select * from [DeletedHistory]
　　/*
　　(2 行受影响)
　　(5 行受影响)
　　(1 行受影响)
　　(1 行受影响)
ID DeleteID TableName DeleteDate
----------- ---------------------------------------- -------------------- -----------------------
1 A001+1 SOMX 2010-07-28 12:38:56.623
　　(1 行受影响)
　　
(2 行受影响)
　　(2 行受影响)
　　(1 行受影响)
　　(1 行受影响)
　　(1 行受影响)
ID DeleteID TableName DeleteDate
----------- ---------------------------------------- -------------------- -----------------------
1 A001+1 SOMX 2010-07-28 12:38:56.623
2 A002+2 SOMX 2010-07-28 12:38:56.640
3 A002+1 SOMX 2010-07-28 12:38:56.640
4 A002 SO 2010-07-28 12:38:56.640
　　(4 行受影响)
　　*/