Go to: cascade deletion example

Compared with Oracle 8i, sqlserver 2000 imposes too many restrictions on cascading deletion.

1. Table auto-join does not support cascading deletion or Cascading update
2. When a table has multiple columns associated with another table at the same time, multiple cascading deletion is not supported.
3. What are the limitations on cyclic cascade deletion?
4. .......

There are a lot of restrictions. It is a lot of trouble to migrate Oracle to sqlserver, especially for cascading deletion of Self-join tables (such as department tables) used to construct tree structures.

Consider it. For many cascade deletions not supported by sqlserver, only the original method is used, instead of foreign key Association, but the trigger is used to solve the problem.

The following is a verified trigger implementation example (self-Association example)

All integrity checks after the foreign key Association is abandoned must be implemented through triggers.

-- Delete cascade deletion: Delete the records first, and then delete all cascade records. Recursive triggers are used. Of course, the database must support the recursive trigger function (enable the recursive trigger function in database attribute settings ), however, sqlserver only supports 32-level recursion.
If exists (Select name from sysobjects
Where name = 'del _ documenttype_001 'and type = 'tr ')
Drop trigger del_documenttype_001
Go
 
Create trigger del_documenttype_001
On DBO. documenttype
For Delete
As
If (select count (*) from deleted)> 0
Delete from DBO. documenttype where upno in (select no from deleted)
Go

 

-- Insert data insert: verifies the association logic of Foreign keys, and throws error messages.
If exists (Select name from sysobjects
Where name = 'ins _ documenttype_001 'and type = 'tr ')
Drop trigger ins_documenttype_001
Go
 
Create trigger ins_documenttype_001
On DBO. documenttype
For insert
As
If (select count (*) from inserted where upno is not null
And (upno = No or upno not in (select no from DBO. documenttype)> 0
Begin
Delete from DBO. documenttype where no in (select no from inserted)
Raiserror ('the referenced table DBO. documenttype does not have a primary key or candidate key that matches the list of referenced columns of the foreign key upno. ', 16,1)
End
Go

-- Update data update. If a foreign key is updated, verify that the foreign key exists.
If exists (Select name from sysobjects
Where name = 'update_documenttype_001 'and type = 'tr ')
Drop trigger upd_documenttype_001
Go
 
Create trigger upd_documenttype_001
On DBO. documenttype
For update
As
If (select count (*) from inserted where upno is not null
And (upno = No or upno not in (select no from DBO. documenttype)> 0
Begin
Set identity_insert DBO. documenttype on
Delete from DBO. documenttype where no in (select no from inserted)
-- Insert into DBO. documenttype select * From inserted
Select * into DBO. documenttype from inserted
Set identity_insert DBO. documenttype off
Raiserror ('the referenced table DBO. documenttype does not have a primary key or candidate key that matches the list of referenced columns of the foreign key upno. ', 16,1)
End
Go