SQL Server triggers

1. Theory OF triggers:

A trigger (Trigger) is a special type of stored procedure that is performed on a user's data on a table
A program that is triggered when an UPDATE, INSERT, and DELETE operation is performed. Triggers help to force references to end
Integer to preserve defined relationships between tables when rows are added, updated, or deleted in a table.

2. How the Trigger works:

The name of the ① trigger.
② the action in the trigger.
③ the execution of the trigger action.

3. Create a trigger:

You can create a trigger from the Create TRIGGER statement in the syntax format:

CREATE TRIGGERtrigger_name on{Table | View } [With encryption]{{ {  for |After|INSTEAD of} {[INSERT] [ , ] [UPDATE] } [With APPEND][Not for REPLICATION]  as{Batch| IF UPDATE(column)[{and | OR} UPDATE (column)]}

The parameters are described as follows:

①trigger_name: The name of the trigger. Trigger names must conform to the rules for identifiers, and in the database
Must be unique, you can choose whether to specify the trigger owner name.
②table | view:The table or view on which the trigger is executed, sometimes called a trigger table or a trigger view.
③with Encryption:Encrypt the syscomments table that contains the text of the CREATE TRIGGER statement
Entry.
④after:Specifies that the trigger only fires after all operations specified in the triggering SQL statement have been executed successfully
Hair If you specify only the FOR keyword, after is the default setting. After triggers cannot be defined on the view.
⑤instead of:Specifies that a trigger is executed instead of executing a trigger SQL statement, which replaces the trigger statement's
Operation. On a table or view, you can define a INSTEAD of trigger, but not on the with CHECK
option is defined on an updatable view.
⑥{[DELETE] [,] [INSERT] [,] [UPDATE]}:Specifies which statements to execute on a table or view
At least one option must be specified for a live trigger keyword. In a trigger definition, you allow the use of any combination of
These keywords. If more than one option is specified, separate the options with commas.
⑦with APPEND:Specifies that additional triggers for existing types should be added. Only if the compatibility level is 65 or
Lower, you need to use the optional clause. With APPEND cannot be used with INSTEAD of triggers;
Or explicitly declare an after trigger, and you cannot use the clause.
⑧not for REPLICATION: The touch should not be executed when the replication process changes the table involved in the trigger.
Hair Generator.
⑨as:The action to be performed by the trigger.

4. Trigger instance:

Create TableStudent (Studentnoint Primary Key,--School Number            )        Create TableCOURSE (Coursenoint Primary Key,--Course NumberStudnetidint, use a trigger, change student number, synchronize changes to the course number of a student's elective course,Create Triggertri_updatestudent onStudent--create a trigger in Studnet         for Update                       --Triggering Events         as                               --things to do after an event is triggered        if Update(Sudentid)begin        UpdateCOURSESetStudentID=I.studentid fromCOURSE cr,deleted D,InsertI--deleted and inserted temporary tables        WhereCr. StudentID=D.studentid

Extended reading:

Examples of SQL triggers explained: http://www.cnblogs.com/xianqingzh/archive/2009/04/30/1447143.html

DML Trigger: http://msdn.microsoft.com/zh-cn/library/ms178110.aspx

SQL Server triggers