Sqldatabase-based cascading deletion and cascading update

Cascading Deletion

Delete a row that contains the primary key value. This value is referenced by a foreign key column in an existing row of another table. In cascading deletion, all rows whose foreign key value references the deleted primary key value are also deleted. Syntax:

FOREIGN KEY(COLUMN[,...n])REFERENCES referenced_table_name[(ref_column[,...n])][ON DELETE cascade][ON UPDATE cascade]

Note: column name referenced_table_name: name of the primary key table referenced by the foreign key ref_name: name of the primary key column of the Table to be referenced by the foreign key on Delete: delete cascade on Update: Update Cascade

SQL cascading deletion -- deleting a master table and deleting a slave table -- deleting a table with a primary foreign key relationship

-- Create Table Category (ID int identity (1, 1) primary key, [name] varchar (20) not null) -- Create Table News (ID int identity (1,1) primary key, title varchar (10) Not null, [content] Text not null, createtime datetime not null, caid int not nullforeign key (CAID) References category (ID) on Delete cascade) -- Create Table comment (ID int identity () primary key, [content] Text not null, createtime datetime not null, userip varchar (15) not null, newsid int not nullforeign key (userid) References News (ID) on Delete Cascade

The diagram is as follows:

One news category corresponds to 0 or more news, and one news corresponds to 0 or more comments. When these three tables are created, the external primary key constraints and cascade deletions from the news table to the category table are created, as well as the external primary key constraints and cascade deletions from the comment table to the news table. In this way, as long as the records in the category table are deleted, the corresponding records in other tables will also be deleted.

Set cascading deletion through triggers

The example is the same as above. When creating a table, you can use a trigger instead of cascading deletion. Create an instead of trigger in the category table to achieve cascading deletion.

Use [newssystem] Go/****** object: trigger [DBO]. [trigcategorydelete] script Date: 03/06/2012 20:28:03 *****/set ansi_nulls ongoset quoted_identifier ongoalter trigger [DBO]. [trigcategorydelete] on [DBO]. [category] instead of deleteas begindeclare @ caid intselect @ caid = ID from deleted -- Delete comment delete comments where newsid in (select newsid from news where caid = @ CAID) -- delete news Delete where caid = @ caid -- delete category where id = @ caidend

In this way, when the records in the category table are deleted, the records in the corresponding table are deleted after the trigger is executed to achieve cascading deletion.

Cascade update is similar to cascade deletion. Link: http://www.zblog.us/programing/ SQL /sql-delete-update.html
| Zhao Jie's blog