[SQL] -- triggers, SQL triggers

[SQL] -- triggers, SQL triggers
Concept

Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events, its execution is not called by a program, nor is it manually started, but triggered by an event. For example, when a table is operated (insert, delete, update), it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. -- Baidu encyclopedia

In fact, triggers are similar to programming-language events, but they are triggered by one thing ...... it is similar to the stored procedure and can quickly and completely implement a series of operations.

Example

-- ===================================================== ====== -- Author: zhao Han -- Create date: 16:51:25 -- Description: delete A category trigger -- ========================================== ========= create trigger [dbo]. [trigCategoryDelete] ON [dbo]. [categoryinfo] instead of DELETE -- replace the delete operation AS BEGINdeclare @ caId intselect @ caId = id from deleted -- type number to be deleted -- DELETE comment delete commentinfo where newsId in (select newsId from newsinfo where caId = @ caId) -- delete news delete newsinfo where caId = @ caId -- delete category delete categoryinfo where id = @ caIdEND

To delete a category in the news publishing system, you must delete the category together with the news content under the category and comments of the news content. Because they have a relationship between primary and Foreign keys, when you want to delete a category, the order of deletion should be: Comment, news content, and category. Therefore, you cannot trigger an event in the trigger by deleting the information:

instead of  DELETE

. This means that the deletion event mentioned above is replaced with the following event.
Therefore, the above trigger means:
This trigger is triggered when a category is deleted, but this trigger is not deleted yet. Replace the following three Delete statements.
At this time, the information to be deleted has been added to deleted. Therefore, select the category number from deleted.
Find the news number by category number, find the Comment number by news number, and delete the information to be deleted in the order of comment-news-category.

In this way, once triggered, a series of operations are completed, ensuring data integrity and high efficiency ~~