SQL Server triggers

Brief Introduction:

A trigger is a special kind of stored procedure that is executed automatically only when a particular event occurs. Stored procedures and triggers are a collection of SQL statements and Process Control statements that are called directly by the name of the stored procedure, and the trigger is executed primarily by the time it is triggered.

categories of triggers:

Triggers include DML triggers and DDL triggers. DML triggers are a common trigger that executes automatically when data manipulation language (DML) events occur in the database server; a DDL trigger is a new type of trigger that fires when responding to data definition language (DDL) statements and is typically used to perform administrative tasks in the database. Today we mainly study DML triggers, which are divided into after triggers and instead OF triggers two classes.

An after trigger is an execution that is activated after the record has been changed, that is, when the event has ended, the trigger is triggered, and the INSTEAD of trigger is used to replace the original operation, which is triggered before the event occurs, so that it does not execute the original SQL statement, but instead follows the definition in the trigger.

Trigger conditions:

DML triggers are activated in response to update, INSERT, DELETE statements, and DDL triggers are activated in response to statements such as Create, ALTER, DROP, GRANT, DENY, REVOKE, update, and statistics.

Mind Mapping:

To create a trigger:

Simple application:

In the case of the beef Brisket news release system to delete news categories while deleting news and comments, for example, because there are foreign key constraints, delete the news category and delete the category of news and comments. Since after triggers are executed after the record has been changed, we use the instead OF trigger to replace the previously pure Delete category operation with the deletion of comments, news, and categories. This will prevent the foreign key constraint from being deleted.

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >--=============================================--author:< Shao >--Create Date: <2014-8-18>-- description:< Delete Category trigger >--=============================================alter TRIGGER [dbo]. [Trigcategorydelete]   on [dbo]. [Category]   Instead of Deleteas begindeclare @caId intselect @caId =id from deleted--Delete comment Delete comment where newsId in (select newsid< C3/>from News where CaId [email protected])--delete the press, delete, and where [email protected]--delete category delete categories where [email p Rotected] End</span>

the role of triggers:

1) Enforce more complex data integrity than check constraints

2) Use a custom error message3) cascading changes to multiple tables in the database4) Compare the status of data before and after database modification5) Call more stored procedures6) Maintenance of non-normalized dataThere are many advantages of flip-flop, we need to explore the actual application, the above have shortcomings, I hope you criticize correct.