SQL Server triggers

Recently in the customer's project to use the trigger, summed up.

1. What triggers are:

A trigger is a special type of stored procedure that runs when you use one or more data modification operations (UPDATE, INSERT, or DELETE) to modify data in a specified table.

2. Trigger Syntax:

1 CREATE TRIGGER Authors_trigger1 2  on dbo.authors 3  for /*  */4as5    /*  IF UPDATE (column_name) . . . */

Or

1 CREATE TRIGGER Titleview_trigger1 2  on Dbo.titleview 3  of /*  */4as5    /*  IF UPDATE (column_name) . . . */

3. Trigger type

Instead of and after two

Instead of triggers: executed before inserting a real operation.

After: Executes after the specified operation has been performed

1 --1. Instead of Trigger2 Create Triggertrigger_student3  onStudent4instead of Delete5  as6 begin7     Select *  fromStudent8 End9 Ten Delete  fromStudentwhereId= 1 One --perform a delete operation and actually perform a select operation

1 --2. After trigger2 Alter TriggerAfter_trigger3  onStudent4AfterUpdate5  as 6     begin7     Insert  intoStudentinfo (Name,school) (SelectName,school fromInsertedwhereFstatus= 1  andName not inch(SelectName fromstudentinfo))8     End

SQL Server creates two dedicated tables for each trigger: the inserted table and the deleted table.

Inserted: new Value collection after insert and update

Deleted:collection of old values before delete and update

For example : Update the name of the student with ID 1 in the student table named Jake

　　　　　　　　　　　　　　　　　　Student table

Id	Name	Age	School	Fstatus
1	HelloWorld	-	Hunan School	1

1     --Create a trigger2     Create Triggertrigger_13      onStudent4AfterUpdate5      as6         begin7         Select *  frominserted8         End    

The following values are displayed when you update:

1 Update Set = ' Jake ' where = 1

Id	Name	Age	School	Fstatus
1	Jake	20	Hunan School	1

1 Alter Trigger trigger_1 2      on Student 3     Update 4      as 5         begin 6         Select *  from deleted 7         End

The following values are displayed when you update:

Update Set = ' Jake ' where = 1

Id	Name	Age	School	Fstatus
1	HelloWorld	20	Hunan School	1

SQL Server triggers