執行個體說明 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
--結果
/*
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 行受影響)

*/