Instance Description: two ways for SQL Server multi-Table cascade Deletion

-- Instance Description: Two Methods for SQL Server multi-Table cascade deletion

 

-- 1. Trigger-based cascade Deletion
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', 'Company 1'
Go
Create Table tableb (BID varchar (10) primary key, bname varchar (20), Aid varchar (10) References tablea (AID ))
Insert tableb
Select 'b1 ', 'department 1', 'a1' Union all
Select 'b2', 'department 2', 'a1'
Go
Create Table tablec (CID varchar (10) primary key, cname varchar (20), bid varchar (10) References tableb (BID ))
Insert tablec
Select 'c1 ', 'personnel 1', 'b1' Union all
Select 'c2 ', 'staff 2', 'b1' Union all
Select 'c3', 'personnel 3', 'b2' Union all
Select 'c4 ', 'personnel 4', 'b2'
Go
-- Create a trigger
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
-- Test
Delete from tablea
Select * From tablea
Select * From tableb
Select * From tablec
-- Result
/*
Aid aname
------------------------------

(0 rows affected)

Bid bname aid
----------------------------------------

(0 rows affected)

CID cname bid
----------------------------------------

(0 rows affected)

*/

-- 2. Set the external key cascade deletion method and cascade Deletion
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', 'Company 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 ', 'department 1', 'a1' Union all
Select 'b2', 'department 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 ', 'personnel 1', 'b1' Union all
Select 'c2 ', 'staff 2', 'b1' Union all
Select 'c3', 'personnel 3', 'b2' Union all
Select 'c4 ', 'personnel 4', 'b2'
Go
-- Test
Delete from tablea
Select * From tablea
Select * From tableb
Select * From tablec
-- Result
/*
Aid aname
------------------------------

(0 rows affected)

Bid bname aid
----------------------------------------

(0 rows affected)

CID cname bid
----------------------------------------

(0 rows affected)

*/

 

 

 

With logs:

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, -- Order Number
[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, --- Order Number
[Somx_soxh] [float] not null, --- Order Number
[U_somx_whsj] [datetime] Null, --- maintenance time
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 ('deletehistory ') 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, --- Delete the primary key of the record. Join the primary key with "+"
[Tablename] [nchar] (20) Collate chinese_prc_ci_as not null, --- Name of the table in which the record is deleted
[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
-- Test
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 rows affected)

(5 rows affected)

(One row is affected)

(One row is affected)
Id deleteid tablename deletedate
----------------------------------------------------------------------------------------------
1 a001 + 1 somx 2010-07-28 12:38:56. 623

(One row is affected)

(2 rows affected)

(2 rows affected)

(One row is affected)

(One row is affected)

(One row is affected)
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

(Four rows affected)

*/