[Data Center Charging System] use of triggers and data center charging system triggers

[Data Center Charging System] use of triggers and data center charging system triggers

In data center reconstruction, many functions involve operations on multiple tables in the database. For example, after the checkout is successful, the following functions are implemented: 1. Modify the checkout status of the card table. 2. Modify the checkout status of the recharge table. 3. Modify the checkout status of the refund table. 4. Delete the information of cards that are not used and have been checked out in the card table. It is not difficult to implement these functions. It is okay to redefine these methods in layer D, but the efficiency is not high. Is there an efficient way to implement these functions? This involves the use of triggers in the database.

Trigger

1. A trigger is a special stored procedure. It cannot be explicitly called. Instead, it is automatically activated when a record is inserted, updated, or deleted into the table. Therefore, triggers can be used to implement complex integrity constraints on tables.

2. SQL Server creates two special tables for each trigger: The Inserted Table and the Deleted table. These two tables are maintained by the system and exist in the memory instead of in the database. The structure of these two tables is always the same as that of the table to which the trigger is applied. After the trigger is executed, the two tables related to the trigger are also deleted. The Deleted table stores all rows to be Deleted from the table due to executing the Delete or Update statement. The Inserted Table stores all rows to be Inserted into the table for execution of the Insert or Update statement.

3. There are two common triggers: after (for) and instead of for insert, update, and delete events. After (for) indicates that after the code is executed, the trigger is executed; instead of indicates that your operation is replaced by a previously written trigger before the code is executed.

Iv. Syntax

Create trigger name on operation table for | after instead of update | insert | delete as SQL statement

The following is a trigger written for the checkout function. It not only helps me implement the function, but also avoids writing too much code in layer D!
Application:

Create trigger triggerc_UpdateBon Card_info after update as if update ([ICheck]) update ReturnCard_info set ICheck = 'checkout 'where ICheck = 'uncheckout' and UserID = (select top 1 UserID from INSERTED) update Recharge_info set ICheck = 'checkout 'where ICheck = 'uncheckout' and UserID = (select top 1 UserID from INSERTED) delete from Card_info where ICheck = 'checkout 'and UState = 'not use'

Summary:

I learned about triggers when I was learning about databases. At that time, I did not apply this knowledge. I discovered that its functions were so powerful only when it was used in refactoring. Indeed, the real knowledge has been put into practice.