<1> code offset trigger (SQL)

I have heard about the word trigger (first in the database video). After reading the video, I did not have a deep understanding of such things as triggers, stored procedures, and transactions, logs and so on are not checked in detail, and there is no specific attempt or application. That's why today's blog (add the previous ones that were left behind ). When talking about triggers, the first thing that comes to mind is "triggers cannot be used in disorder" and "Use Triggers with caution". However, we can't use these reminders as an excuse for not trying them. To learn how to be brave, let alone the ignorant.

Brief Introduction

1. Concept: A trigger is a special stored procedure (as described in the next blog). Its execution is not called by a program or started manually, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules.

1. Role: The main function is to achieve complex integrity and data consistency that cannot be guaranteed by the primary and Foreign keys. In addition, triggers can implement cascade changes through the relevant tables in the database, so that the integrity constraints of currency cascade references can be executed more effectively. There are also enhanced constraints, tracking changes, and calling stored procedures. Of course, there are many other functions, but they are not available yet. For details, refer to the encyclopedia trigger.

How to Use

Compared with the basic knowledge above, I believe everyone is more concerned about how to use it. The following uses a recharge function in the data room charging system as an example to describe the specific application of the trigger in the system.

1. First, think about where the trigger is used in the recharge function? After recharge is complete, a record is saved to the recharge record table, which contains the latest balance and updates the balance to the card table. That is, when the insert operation is executed, the condition is triggered to execute an Update operation.
2. Create a trigger in the corresponding SQL table. This operation is completed in the recharge record table, so the trigger is created in this table. For details, see:

1. Code for adding a trigger:

-- ===================================================== ====== -- Author: niu Qian -- Create date: 06/09/2014 15:32:21 -- Description: When a recharge record is inserted, automatically update the balance in the card table -- ========================================== ============= alter trigger [dbo]. [CardBalance] -- trigger name ON [dbo]. [Recharge_info] -- trigger position FOR insert -- indicates the action type of the expected trigger to activate AS declare @ Balance varchar (8) -- declare @ CID varchar (8) -- Declaration Parameter select @ Balance = balance from Recharge_info -- assign select @ CID = CID from Recharge_info -- BEGIN to the parameter -- execute the trigger to UPDATE the balance of the card table UPDATE Card_info set Balance = @ Balance where Card_info. CID = @ CID END

 

1. View the running result:

Recharge record table:

 

Card table:

 

 

Use trigger with caution:

The trigger has powerful functions and can easily and reliably implement many complex functions. Why should we use it with caution. The trigger itself is not at fault, but misuse of the trigger will cause difficulties in database and application maintenance. If we rely too much on triggers, it will inevitably affect the database structure and increase the complexity of maintenance. Therefore, although the trigger is good, you should consider it when using it and use it when appropriate.

 

One of the feelings of refactoring is that the amount of code is greatly increased. Write add, delete, modify, and query operations, create entities, write interfaces, layer D, layer B, and layer U, and call them layer by layer. Therefore, we need to reduce the code burden and simplify and reuse the code.

Learning database technology is a good start.

 

PS: About trigger this blog introduces more detailed for everyone to learn: http://www.cnblogs.com/kissdodog/p/3173421.html