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

--实例说明 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

--结果

--二、设置外键级联删除方式，级联删除

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

--结果

带日志：

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]