Atitit... Summary of the trigger mechanism ltrigger mechanic sumup. O8f, ltriggero8f

Atitit... Summary of the trigger mechanism ltrigger mechanic sumup. O8f, ltriggero8f

Atitit... Summary of the trigger mechanism ltrigger mechanic sumup. O8f

 

1. Trigger type 1

2. Implementation Principle 1

3. After trigger Vs Instead Of trigger 1

4. DML triggers Vs DDL triggers 3

5. Trigger and transaction 3

6. Transactions in the three common triggers are described below: 5

. Instead of trigger (previously triggered) 7

8. The trigger has two special tables: The Insert table and the delete table ). 7

9. Copy and migrate table records select into and insert into select 8

10. Refer to 9

 

1. Trigger type

· DML trigger (DML Triggers)

· DDL Triggers)

· Transaction modes)

· Explicit transaction (Explicit Transactions)

· Autocommit Transactions)

· Implicit Transactions)

· Batch-scoped Transactions)

2. Implementation Principle 3. After trigger Vs Instead Of trigger

The After trigger is triggered After processing trigger operations (Insert, Update, or Delete), Instead Of triggers, and constraints. Instead Of is triggered before processing constraints to replace trigger operations. The following two figures describe the sequence Of execution Of the After trigger and the Instead Of trigger.

 

 

Author: old wow's paw Attilax iron, EMAIL: 1466519819@qq.com

Reprinted please indicate Source: http://blog.csdn.net/attilax

 

Figure 1 Figure 2

4. DML triggers Vs DDL triggers

DML triggers operate on Insert, Update, and Delete statements. They can be used as the After trigger and Instead Of trigger.

DDL triggers operate on the stored procedures Of Create, Alter, Drop, and other DDL statements and DDL operations. They can only be used as the After trigger and cannot be used as the Instead Of trigger.

The preceding content describes the content Of the After & Instead Of trigger in the DML trigger. The following describes the DDL operation sequence:

Figure 3.

As shown in figure 3, the Inserted & Deleted process is not created in the DDL trigger. Let's test it through a simple example.

5. Trigger and transaction

It can be seen that the Update Contact triggers the tr_Contact trigger operation, and the Rollback Tran action in the trigger causes the Update statement outside the trigger to execute Rollback, And the Begin Tran statement after the Rollback Tran statement, it is mainly used to maintain the integrity of the entire transaction. To better understand this process, I have simulated the transaction starting and ending process in a trigger.

Figure 4.

On SQL Server 2005 and SQL Server 2008, you can see 4. Results. In earlier versions of SQL Server, errors may occur. In any case, SQL Server Rollback Tran outside the trigger. The following is an example of an error message.

 

In any case, SQL Server Rollback Tran outside the trigger. The following is an example of an error message.

There is no Begin Tran statement action in the trigger, and rollback can also be performed outside the trigger. Here, we can query the table data and determine by @ Trancount.

In fact, in the above example, the Update statement is executed in the Autocommit Transactions mode. After the Rollback Tran in the trigger, whether there is a Tran in Tran, all transactions will be handed back to SQL Server for automatic transaction management. Of course, in DML triggers, you can use Explicit Transactions or enable Implicit Transactions to control them, of course, you can also apply it to Batch-scoped Transactions. Here, the relationship between a trigger and a transaction is described in the example of enabling an Implicit transaction (Implicit Transactions.

Here, do you find a very interesting problem: Before the trigger executes Insert ContactHIST, @ Trancount = 1. After the Insert statement is executed, @ Trancount is still 1, after the trigger updates the Contact, @ Trancount is changed to 2 ,. It can be understood that if you issue a Tran in the trigger, SQL Server will create a nested transaction. When you block Begin Tran behind Rollback Tran in the trigger, error 3609 occurs, for example,

 

 

6. Transactions in the three common triggers are described below:

Figure 5. Figure 6. Figure 7.

Figure 5. Description of the trigger containing Begin Tran ...... Condition of Commit Tran,

Figure 6. Description: The trigger contains the Save Tran savepoint_name ...... In the case of Rollback Tran savepoint_name, the Rollback Tran in the trigger only rolls back the specified storage point and does not affect the Commit Tran Or Rollback Tran operation outside the trigger.

Figure 7. Describe the situation where the trigger contains Rollback Tran. no matter whether the trigger contains Begin Tran, error 3609 will occur and batch processing will be aborted.

Note: DDL trigger operations can be performed in a trigger. You can use commands such as Rollback. However, serious errors may cause automatic Rollback of the entire transaction. You cannot roll back the Alter Database event that occurs in the DDL trigger body. Use Rollback… in a trigger... Begin Tran may cause unexpected results. Do not directly use Rollback in the trigger without confirmation or testing... Begin Tran processing method. Especially for Create Database events, the results are different in SQL Server 2008 and SQL Server 2005 environments.

 

. Instead of trigger (previously triggered)

 

The after trigger requires that the trigger be triggered only after an insert, update, or delete operation is executed and can only be defined on the table. The instead of trigger does not execute the defined operations (insert, update, delete) but only executes the trigger itself. You can either define an instead of trigger on a table or a view.

 

 

8. The trigger has two special tables: The Insert table and the delete table ).

These two tables are logical tables and virtual tables. Two tables created by the system in the memory are not stored in the data base. Both tables are read-only and can only read data but cannot modify data. The results of these two tables are always the same as the structure of the table applied by the modified trigger. After the trigger is complete, the two tables will be deleted. The data in the Inserted Table is the Inserted or modified data, and the data in the deleted table is the data before or after the update.

 

Table operations	Inserted logical table	Deleted logical table
Insert)	Store added records	None
Delete)	None	Store deleted records
Update)	Store updated records	Store records before update

When updating data, you delete the table record and add a record. In this way, the inserted and deleted tables have updated data records. Note: The trigger itself is a transaction, so you can perform some special checks on the modified data in the trigger. If not, you can use transaction rollback to cancel the operation.

9. Copy and migrate table records select into and INSERT INTO SELECT

Insert is a common statement in T-SQL. Insert INTO table (field1, field2 ,...) values (value1, value2 ,...) this form is essential for application development. However, during the development and testing processes, we often encounter situations where table replication is required, such as copying some of the data fields of Table 1 to table 2 or copying the entire table 1 to table 2, in this case, we need to use the select into and insert into select table copy statements.

 

Select into to create a new table... insert into select can exist in the table insert data... therefore, it is still commonly used to insert into select...

 

 

Alter trigger [dbo]. [trg_inst]

ON [dbo]. [t_mb_weixinuser]

AFTER INSERT

AS

BEGIN

-- Set nocount on added to prevent extra result sets from

-- Interfering with SELECT statements.

Set nocount on;

Insert into wechatuhstry (userid, [subscribe], [openid], [subscribe_time], [groupid], [createTime], [updateTime], nickname) select userid, [subscribe], [openid], [subscribe_time], [groupid], [createTime], [updateTime], nickname from inserted;

-- Insert statements for trigger here

 

END

 

# ---- Best Practices

The id auto-incream function of the copy2 table is recommended to be disabled. The id is the same as the id in the middle... share the same primary key.

Text cannot be copied, so you have to specify the field...

 

 

# --- Rest-style security can be copied multiple times

Insert into wechatuhstry

Select top 100000 * from t_mb_weixinuser where UserID not in

(Select UserID from wechatuhstry)

 

10. Reference

SQL Server trigger-hoojo-blog .htm

Understand the transactions in SQL Server triggers and triggers-OK _008-blog .htm