"Room charge Personal Edition" trigger and stored procedure

This blog is mainly written about the application of triggers and stored procedures, examples, advantages, differences and drawbacks, and finally through the overall analysis, summed up who is better.

Trigger

Defined

Triggers are a mechanism for enforcing business rules and data integrity that automatically takes effect when data in a specified table changes, in response to an INSERT,UPDATE , or DELETE statement. This is also the main role of it!

The most common use of DML triggers is when a trigger is enabled when a DML event occurs in the database , it modifies the insertof the data in the table or view, update,delete statement:

Instance

Below , we use examples to illustrate the specific wording and usage of triggers.

This example is when the card number is being recharged, the balance in the student's table should be updated while the data is inserted into the recharge table.

--=============================================--author:< Guo Xian up >--Create Date: <2015.6.7>--Description: < after a successful recharge, change the balance in the student table >--=============================================--because the trigger has been modified, create automatically changes to Alteralter TRIGGER [dbo]. [Tr_updatebalance]--trigger name   on  [dbo].[ T_recharge]--Create location   after insert--then insert data after as--define variable declare @InRows int, @CardNO varchar, @Recharge Numeric (18,1)- -Assign a value to a variable by querying select @Recharge =recharge from inserted--inserted represents a virtual table to hold the data we just inserted select @CardNO =cardno from inserted--Query the number of top-up records of the number    select @InRows =count (*) from T_recharge  where Cardno [email protected]--if the card number recharge record is greater than 1, Description is not a new registered user, perform an update if @InRows >1beginset NOCOUNT on;--Change the balance update t_student set Cash=convert (numeric,cash) [email protected] where [email protected] END

Advantages

We use the trigger of course because it has a lot of advantages, can give us convenience, below I will list its benefits:

High efficiency

The cost of a trigger is very low, and the time it takes to run the trigger is mostly spent referencing other tables that are stored in memory or on disk.

Cascade Modification

Cascading modifications of multiple tables can be implemented. When it is necessary to modify the data of a table, the corresponding data of its related table will be automatically added and modified to ensure the consistency of the data.

Simple and convenient

The use of triggers can write a lot less code, the event way to deal with, when the data changes, the self-action processing, so that we operate the table more simple.

Stored Procedures

Defined

A stored procedure is a set of SQL statements that are designed to accomplish a particular function , and the user executes it by specifying the name of the stored procedure and giving the parameter ( if the stored procedure has parameters ) . Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.

We can also understand that it is a set of SQL statements that are written, just like a function method, that are written to be sealed and called directly when needed.

Instance

--=============================================--author:< Guo Xian up >--Create Date: <2015-06-10 15:08:13>-- description:< the card number from the student table, adds data to the card return table, and then deletes the data from the student table, top-up table and the on-board table >--=============================================alter PROCEDURE [dbo]. [proc_exitcard]--definition variable @cardno varchar (10), @UserID varchar (TEN), @Name varchar (@ReturnCash varchar), @ Exitcarddate varchar, @ExitCardTime varchar, @IsCheckOut varchar asbeginset NOCOUNT on;--Add data to the student table insert Into T_exitcard (cardno,userid,name,returncash,exitcarddate,exitcardtime,ischeckout) VALUES (@CardNO, @UserID, @Name , @ReturnCash, @ExitCardDate, @ExitCardTime, @IsCheckOut)--Delete student table, top-up table, table data Delete t_recharge where [email protected] Delete t_line where [email protected]delete t_student WHERE [email protected]end

Advantages

Simple and convenient

Stored procedures are written in the database beforehand , after the first compilation after the call does not need to compile , run faster, reduce network traffic. You can set parameters to reuse the same stored procedure , depending on the parameters passed in.

Increase efficiency

When complex operations are performed on a database, such as adding and removing multiple data tables, we can encapsulate them with stored procedures and combine them with the transactions provided by the database. In this way, the efficiency can be greatly improved! Furthermore, from the point of view of code, it is separated from the program code, which improves the readability of the program code.

Improve security

You can set permissions that only a batch of users can use, because it is a parameterized query that prevents SQL injection; the stored procedure can accept parameters, output parameters, return single or multiple result sets, and return values, and return the cause of the error to the program.  

Triggers VS stored Procedures

Same point

When a user executes an event and the database needs to be updated a long time, we can consider using them. This allows the D layer to easily and reliably implement many complex functions, improving efficiency and code readability.

Different points

Execution conditions

Triggers are executed primarily through event execution triggers, and stored procedures can be called directly by the name of the stored procedure name.

Flexibility

Triggers are enforced, and the user cannot bypass the trigger as long as the trigger condition is met. In contrast, stored procedures are relatively flexible, and in our code, you can decide when to call a stored procedure. A stored procedure is executed as a function with an execute statement.

Disadvantages

Although both powerful, each thing has two attributes, the advantage must have the disadvantage!

Trigger execution is conditional, is not controlled by the user, maintenance is difficult; Because the stored procedure is only once compiled and then just called, making later maintenance work challenging.

Summarize

In general, the use of triggers and stored procedures, improve the efficiency of the system execution, enhance readability, make our work convenient and simple, is worth advocating! The two have some similarities, and if you really want to use them, you should prefer the stored procedure. Of course, there is no best, only more suitable.

As the master suggested that I use the prototype map, so, has been learning recently, perhaps the next blog is it, please look forward to it!!!

"Room charge Personal Edition" trigger and stored procedure