Data room charging system trigger

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.