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 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 [email protected]
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.

<Span style = "font-size: 14px;"> use [jf_charge] Go/****** object: trigger [DBO]. [tr_charge_u] script Date: 08/18/2014 15:34:42 *****/set ansi_nulls ongoset quoted_identifier ongocreate trigger [DBO]. [tr_charge_u] <span style = "white-space: pre"> </span> -- create a trigger <span style = "white-space: pre "> </span> on [DBO]. [t_recharge] <span style = "white-space: pre"> </span> -- In the t_recharge table for insert <span style = "white-space: pre "> </span> the operation as required to insert data -- defines some parameters declare @ cardid char (10) <span style =" white-space: pre "> </span> -- card number declare @ cash decimal (10, 2) <span style =" white-space: pre "> </span> -- card balance declare @ addmoney decimal (10, 2) <span style =" white-space: pre "> </span> -- Recharge Amount if Update (cash) beginselect @ cardid = cardid from inserted <span style =" white-space: pre "> </span> -- select the card number in the recharge table select @ addmoney = cash from inserted -- select @ Cash = t_card.cash from t_card <span style =" white-space: pre "> </span> -- select the card balance set @ date = convert (varchar (10), getdate (), 111) -- Get the date set @ time = convert (varchar (8), getdate (), 108) -- Get the time update t_card set [email protected] [email protected] Where cardid [email protected] End </span>

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 [email protected]: The logic problem is solved by ourselves. In this way, we can write two or three more statements to combine, but it's okay to use one, 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 small demoinsert triggers for 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.