Data room charging system trigger

The earliest Contact trigger was seen in Yan jianling's video, but he did not understand it at all. However, when the data center charging system was implemented, it turns out that SQL Server has such a powerful feature, which is simply the best group of two siblings in combination with stored procedures. First, let's look at what the trigger is? Trigger, because the Stored Procedure +

The earliest Contact trigger was seen in Yan jianling's video, but he did not understand it at all. However, when the data center charging system was implemented, it turns out that SQL Server has such a powerful function, which is simply the best two-person group together with stored procedures. First, let's look at what the trigger is? Trigger, because the Stored Procedure +

The earliest Contact trigger was seen in Yan jianling's video, but he did not understand it at all. However, when the data center charging system was implemented, it turns out that SQL Server has such a powerful function, which is simply the best two-person group together with stored procedures.

First, let's look at what the trigger is?

Trigger, Because I first know the Stored Procedure + transaction, so here I call it the stored procedure's little brother. As to why, because they are really like, for the stored procedure, what can we do in logging?

A trigger is a method that SQL Server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. The trigger cannot be executed directly. It can only be triggered by the Insert \ Update \ Delete event on the table. Nor can it pass or accept parameters.

Triggers can query other tables and contain complex SQL statements. They are mainly used to force complex business rules or requirements. For example, you can control whether to insert a new order based on the current account status of the customer. For example, I have two tables, one is the Recharge_Info table and the other is the Student_Info table. If we want to recharge, we need to perform two actions, the first is to add a record to the student table and the second is to add a record to the recharge table. To complete this operation, we need to update and insert information in two steps, that is to say, we will insert Recharge_Info into (cardID, addMoney...) in this way ...) values (@ cardID, addMoney ...) update Student_Info set Cash = Cash + addMoney where cardID = @ cardID
If this process needs to be executed multiple times, we need to combine the process with two statements each time. Is it a little troublesome? So now we have the trigger process, let's demonstrate the same Implementation of the trigger for everyone.

Create trigger [dbo]. [TR_charge_U] -- create a trigger ON [dbo]. [T_Recharge] -- In the T_ReCharge table for insert, perform the AS operation to insert data -- define some parameters declare @ cardID char (10) -- card number declare @ cash decimal (10, 2) -- card balance declare @ addmoney decimal (10, 2) -- Recharge Amount if UPDATE (cash) BEGINselect @ cardID = CardID from inserted -- select the card number in the recharge table select @ addmoney = cash from inserted -- select the Recharge Amount in the recharge table select @ cash = T_card.cash from T_Card -- select the balance in the card update T_Card set Cash = @ cash + @ addmoney where cardID = @ cardID END

Here we will explain how this process works. The first statement we want to execute is insert Recharge_Info into (cardID, addMoney ...) values (@ cardID, addMoney ...), then, when you insert data, the trigger will save the information to Inserted. Then, we can extract the data from the trigger as needed to update the table to be updated. In the trigger, we have two tables, one is the deleted table and the other is the Inserted Table. These two tables are virtual tables. After they are used up, the database will be automatically deleted. Below I will show their usefulness in the form of tables

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

So when we execute an operation, people will say that the trigger operates at the same time. Actually, it is not. The trigger will insert Recharge_Info into (cardID, addMoney ...) values (@ cardID, addMoney ..., update Student_Info set Cash = Cash + addMoney where cardID = @ cardID: The logic problem is solved by ourselves. In this way, we can save the trouble by writing two or three statements to combine them, so how many times? However, the more triggers, the better, because they are a piece of code embodied in the database and will not be displayed in the program. Therefore, adding a few triggers in the development phase may not be a problem, however, if there is a problem with our system in the future, it would be a little difficult to modify this aspect. The more triggers, the more difficult the logic will be. Therefore, we recommend that you use them, suitable for use. Finally, we will present some Demo Insert triggers for creating various triggers.

Create trigger tgr_classes_inserton classes for insert -- insert trigger as -- Define the variable declare @ id int, @ name varchar (20), @ temp int; -- query the inserted records in the inserted Table. select @ id = id, @ name = name from inserted; set @ name = @ name + convert (varchar, @ id ); set @ temp = @ id/2; insert into student values (@ name, 18 + @ id, @ temp, @ id );

Delete trigger

Create trigger tgr_classes_deleteon classes for delete -- delete the backup data in the trigger as print ...... '; If (object_id ('classesbackup', 'U') is not null) -- classesBackup exists, insert data directly into classesBackup select name, createDate from deleted; else -- no classesBackup is created, and then select * into classesBackup from deleted; print 'is successfully inserted! ';

Update trigger

Create trigger tgr_classes_update on classes for update as declare @ oldName varchar (20), @ newName varchar (20); -- select @ oldName = name from deleted; if (exists (select * from student where name like '%' + @ oldName + '%') begin -- select @ newName = name from inserted; update student set name = replace (name, @ oldName, @ newName) where name like '%' + @ oldName + '%'; print 'cascade data modification successful! '; End else print' no need to modify the student table! ';

A trigger, a stored procedure, and a fully automatic and arbitrary call can make the program run faster and consume less resources. Although it looks very similar, the function seems to reduce a lot of pressure on us, but we should be cautious when writing, or when designing the database, otherwise, we will bring unimaginable pain to the future operation of the software.