SQL Server triggers

What is a trigger?

　　In SQL Server is a certain operation on a table, triggering a certain condition, thus executing a program. A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ an update record or a record is deleted. So triggers can be used to implement complex integrity constraints on a table.

SQL Server creates two dedicated tables for each trigger: the inserted table and the deleted table. Deleted and inserted represent the table for the departure event "old record" and "New record" respectively. These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of the two tables is always the same as the structure of the table that the trigger acts on. The two tables associated with the trigger are also deleted after the trigger execution is complete.

Instead of, after, for triggers

　Instead of triggers are used to replace T-SQL statements that cause trigger execution. In addition to tables,instead of triggers can also be used for views that extend the update operations that the view can support.

After triggers are executed after a insert,update or deleted statement, and actions such as constraint checking occur before the after trigger is activated. After triggers can only be used for tables. Each modification action (insert,update and delete) of a table or view can have a instead of trigger, and each modification action of a table can have more than one after trigger.

The for trigger is similar to after. The for is executed at the same time as the insert,update or deleted statement.

Execution process of triggers

If a insert﹑update or DELETE statement violates the constraint, then the after trigger does not execute because the check for the constraint occurs before the after trigger is agitated. So after triggers cannot go beyond constraints.

The Instead of trigger can be executed in place of the action that fired it. It is executed when the inserted table and the deleted table have just been created and no other operations have occurred. Because the instead OF trigger executes before the constraint, it can perform some preprocessing on the constraint.

Description

This article only explores after triggers, as far as instead-of triggers are not covered in this article. I also skipped the syntax for after triggers, focusing on the areas you should be aware of when applying triggers.

first: Triggers do not fire once for each record. For example, if we update or delete more than one record in a transaction, the trigger will only fire once, but it will normally reflect all affected rows.

Second: Use a trigger must be clear two virtual table:
1:inserted, all records that are inserted in the transaction.
2:deleted, all records that were deleted in the transaction.
Description: The updated table does not exist in the database because the update of the data is the process of first deleting and then inserting. With the above two dummy tables, we can do other things on the affected rows, such as using data from inserted to update some of the field values in this record, after entering the data for the integration hotel.

Third:after, in the literal sense should be after the meaning of an operation, after the trigger is the data successfully inserted into the data table after the operation, if the data inserted failure, the corresponding trigger is not raised.

Definition and creation of triggers

Triggers can be created in Query Analyzer, or by right-click "All Tasks" and "Manage triggers" on the table name, but all are written in T-SQL statements, but in the Query Analyzer you first determine the current operation's database.

Creating a trigger with Create TRIGGER　　

CREATE TRIGGERTrigger_name//Trigger name on {table | view}      //view or table[With encryption]       // Encryption { {{}For | After | INSTEAD of} {[ INSERT] [DELETE] [UPDATE] }[With APPEND]        //Specifies that additional triggers for existing types should be added[Not for REPLICATION]  //Indicates that the trigger should not be executed when the replication process changes the table involved in the trigger.  as                      //Is the action to be performed by the trigger. [{IF UPDATE ( column ) //tests the INSERT or UPDATE operation on the specified column and cannot be used for the DELETE operation. [{and | OR} UPDATE ( column )]  //is the column name to test for the INSERT or UPDATE operation[. .. n]| IF(columns_updated() updated_bitmask)//if (columns_updated () tests whether the column mentioned is inserted or updated and is used only for insert or UPDATE trigger, Updated_bitmask is the bitwise operator used to compare operations. Column_bitmask[... n]      // is an integer bitmask that represents the actual updated or inserted column }] Sql_statement[... n]       // is the comparison operator. }}

Example: when inserting data into the table "meter 0261", check whether the number exists in the table "Count 026", if there is an insert operation, otherwise it is not inserted.

CREATE TRIGGER [Checkid]  on [dbo].[Count 0261]INSTEAD of Insert asIF  not EXISTS(SELECT *  fromCount 026WHERESchool Number=(SELECTSchool Number fromINSERTED)) BEGIN    ROLLBACK TRANSACTION    PRINT 'the number of records to be processed does not exist! '  ENDELSE  BEGIN    INSERT  intoCount 0261Select *  frominsertedPRINT 'records have been successfully processed! '  END

Example: Setting an after type insert trigger on an order table is used to automatically calculate the statistic value into the order statistics when the record is inserted.

CREATE TRIGGER [OrderInsert]  on [dbo].[Order Form] AfterINSERT asDECLARE @bookid int,@ordernum int,@num intSELECT @bookid =Book Number,@ordernum =Number fromINSERTEDSELECT @num = Count(Book number) fromOrder StatisticsWHEREBook Number=@bookidIF @num = 0  --The book was not found and the record was inserted  INSERT  intoOrder StatisticsVALUES(@bookid,@ordernum)ELSE  --find the book, update the record  UPDATEOrder StatisticsSETTotal order Quantity=Total order Quantity+ @ordernum WHEREBook Number= @bookid

SQL Server triggers