Triggers implement cascading modification of related tables in the database

Data Table relationship diagram:

The database has three tables: News, comment, news, and category. The categoryid of the news table is associated with the ID foreign key of the category table, newsid in the comment table is associated with the ID foreign key in the News table.

Run the following command in SQL:

delete from category where Id =1

If the news table and comment table have an association with ID = 1 in the category table, a message is displayed, indicating that the delete statement conflicts with the foreign key and execution fails. The trigger is used to implement cascading modification of database-related tables by solving foreign key associations.

It is easy to create a trigger in the category table to be deleted and design an SQL statement to be deleted from bottom to top:

Use [newssystem] Go/****** object: trigger [DBO]. [trigcatgorydelete] script Date: 07/26/2012 19:44:26 ******/set ansi_nulls ongoset quoted_identifier ongo -- ================ =================================-- Author: jesse -- create Date: 2012-7-26 -- Description: delete A category trigger -- ========================================== ========= alter trigger [DBO]. [trigcatgorydelete] on [DBO]. [category] instead of deleteas begin -- delete news where categoryid = (select ID from deleted) -- declare @ ID int -- select @ ID = ID from deleted -- delete news where categoryid = @ ID -- delete category where id = @ iddeclare @ caid intselect @ caid = ID from deleted -- delete comment on Delete comment where newsid in (select newsid from news wherer caid = @ CAID) -- delete news where categoryid = @ caid -- delete category where id = @ caidend