SQL Server triggers

Introduction:

A trigger is actually a special kind of stored procedure. It only operates on its own initiative 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. The trigger is run mainly by time.

categories of triggers:

Triggers contain DML triggers and DDL triggers. A DML trigger is a common trigger that runs on its own initiative when data Manipulation Language (DML) events occur in database server; a DDL trigger is a new type of trigger that fires when it responds to data definition language (DDL) statements. Typically used to run administrative tasks in a database. Today we focus on DML triggers. It is divided into after trigger and instead OF trigger two classes.

After triggers are the runs that are activated after the record has been changed. That is, the event has ended trigger is triggered; the INSTEAD of trigger is used instead of the original operation to be triggered before the event occurs so that it does not run the original SQL statement. Instead, it 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:

Take the Beef brisket press release system to delete news categories at the same time delete news and comments for example. Because there are foreign key constraints, delete the news category at the same time to delete the category of news and comments. Since after triggers are activated 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< C2/>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 your own defined error message3) Implement cascading changes to multiple tables in the database4) The state of the data before and after the database changes5) Call many other stored procedures6) Maintenance of non-normalized dataThere are many more advantages to triggers. We need to explore in practical applications. There are deficiencies, I hope you criticize correct.??

SQL Server triggers