SQL after trigger Summary

Although I have some knowledge of the SQL Releaser, I have not written it since I have been working for nearly four years. I think many of my friends, like me, although ado.net is often used, it is nothing more than adding, deleting, modifying, and querying operations. The application of the Releaser is much less than the stored procedure. The most recent project requirement reminds me of the application scenarios of the Releaser.

Usually, a company's IT department is divided according to its functions or business. Each department is responsible for some functional modules, and each department is also independent, when the functions provided by other departments cannot fully meet your needs, you may need to help improve the functions by yourself.

Example:Department A is responsible for the hotel business. All hotel queries and order processing are completed by them, and all hotels have unified inventory management. However, some hotels are special and can be changed with points, however, the inventory of such hotels is not the same as that of normal hotels. The only thing in common is that there is a ratio between the price of the points to the price of normal hotels, such as normal hotels (Beijing hotels) 100 yuan per night, so the points Hotel (Beijing Hotel) requires 100*100 points. Previously, the hotel price for point redemption was manually specified when the system entered the price, but now the business is changed, the price needs to be automatically initialized when the Point Hotel is entered, this price is subject to the corresponding normal hotel price.

Problem:How to get the initialization price?
Method 1: ModifyProgram.
Advantage: it is easy to process. When data is input, the price of the corresponding normal hotel is first queried and then written into the database.
Disadvantage: it must be supported by others. If this program is not maintained, the processing will be very difficult.
Method 2: install an additional patch for the original program. After the data is imported into the database, the price is automatically initialized using the trigger.
Advantage: you only need to add an additional trigger, and the modification volume is relatively small.
Disadvantages: disadvantages of relative method 1 are not worth mentioning.

Trigger definition:It is a unique type of process, very similar to events in C. In Asp.net, When you click a button, the corresponding event is triggered. The trigger is associated with the database table. When the table content changes as follows, the trigger is triggered. 1: insert; 2: update; 3: delete.

Note:This article only discusses after triggers. As for instead-of triggers, they are not covered in this article. I also skipped the syntax of the After trigger, focusing on what should be paid attention to when applying the trigger.

 

First, the trigger does not trigger each record once.For example, if we update or delete more than one record in a transaction, the trigger will only trigger once, but it can normally reflect all affected rows.

Second, the trigger must be clear about two virtual tables:

1: INSERTED: All records inserted in the transaction.
2: deleted, all records deleted in the transaction.
Note: the updated table does not exist in the Database, because data update is the process of deleting and then inserting data. With the above two virtual like tables, we can perform other operations on the affected rows, for example, after entering the Point Hotel data, use the data information in inserted to update certain field values of this record.

 

Third: AfterIn the literal sense, it should be after an operation. The After trigger is the operation performed after the data is successfully inserted into the data table. If the data insertion fails, the corresponding trigger will not be triggered.

 

Fourth: the trigger and the developer have different definitions on what is a successful table operation.When 0 records are updated or deleted, the trigger will also be triggered, but obviously this trigger is meaningless for developers. This trigger is meaningless. Second, it occupies system resources. If your data server is very busy and a user is locking the database table, trigger execution usually takes more time than you think. Therefore, we hope to exit the trigger as soon as possible. We can use the followingCodeThis code must be reflected at the beginning of the DEFA statement. Otherwise, @ rowcount will be affected by the previous code.
 

If @ rowcount =   0  
Begin
Print ' Trigger exit '
Return
End

Fourth: Do not use select and print statements in Stored ProceduresAlthough these two statements are very useful during debugging, it is best not to reflect them in the formal launch environment. These statements will generate additional result sets. If they are not processed properly, or if they are not expected to appear, such a result set may affect the failure of the client application.

Fifth, trigger management.

1: Use the sp_helptrigger command to view the trigger associated with a table. Sp_helptrigger table name
2: Use the sp_helptext command to query the code of a trigger. Sp_helptest trigger name.
3: delete trigger, similar to deleting other database objects. Drop trigger name.
4: Modify the trigger is similar to generate the trigger, except that the statement modified is alter trigger.
5: Use the exec sp_rename command to rename the trigger.

Sixth: Transaction Management in the trigger.A trigger is always part of the initialization transaction. The transaction can be explicit or implicit. You can use the following script in the trigger to perform data operations before rolling back and forth. In the previous code, you do not have to display Code such as begin transaction.

If @ Error <>   0  
Begin
Rollback transaction
End

7. Trigger execution sequenceFor an After trigger, a table can correspond to multiple after triggers. How do many triggers control their execution sequence? We can use the exec sp_settrggerorder command to execute the following command:

Code

Exec sp_settriggerorder @ triggername = Trigger name,
@ Order = ' First, last or none ' ,
@ Stmttype = ' Data operation type, such as insert, update, or delete '

8. in which situations do I not need to use a trigger at all:
 
1: Do not use triggers if you can use the functions implemented by constraints.
2: If you can use a stored procedure to complete the task, you do not need to use a trigger.
Note: trigger implementation, debugging, and management are more difficult than stored procedures. If you have a better way to achieve your needs, it is best not to use a trigger unless there is no simpler method than a trigger.

Summary:

When appropriate scenarios are selected, triggers often have unexpected results. This articleArticleI only mentioned some practical knowledge about the after trigger. As for the specific application, the wise man sees wisdom.

 

Author: Jiang min
Source: http://www.cnblogs.com/aspnet2008/