trade-offs between stored procedures and triggers (analysis of advantages and disadvantages)

A

Disadvantages:

1. Portability is the biggest drawback of stored procedures and triggers.

2, occupy the server side too many resources, to the server caused a lot of pressure

3, can not do DDL.

4, the trigger scheduling error is difficult, and the data is easily inconsistent, late maintenance inconvenient.

Advantages:

1, precompiled, has been optimized, high efficiency. Avoids the inefficiency of SQL statements being transmitted over the network and then interpreted.

2. Stored procedures can be reused to reduce the workload of developers.

3, Business logic packaging good, easy to modify.

4, safety. There is no SQL statement injection problem.

Because to share some information about the database stored procedures and triggers knowledge, I think, this is a very detailed technology, generally as long as the use of it will soon grasp the usage of the method. After so many years, I generally in the design of the database will be more or less the use of stored procedures and triggers, the reason is simple: good performance, business is also achieved. But in doing the last project, because the business is very complex, the number of stored procedures and triggers are all up to hundreds, this is a very scary thing, especially in the wrong debugging maintenance, it will think without these things how good. Here's how I'll look at the stored procedures and triggers from an experience point of view.

1, the trigger is a special stored procedure.

This sentence in the textbook will often appear, which means that there is a big connection between the two, my general understanding is that the trigger is a hidden stored procedure, because it does not require parameters, do not need to display the call, often in the case of you do not know the situation has done a lot of operations. From this perspective, because it is hidden and adds to the complexity of the system, it is difficult for a non-DBA to understand the database because it does not feel like it exists. Again, when it comes to complex logic, trigger nesting is not to avoid, if it involves a few stored procedures, plus transactions, and so on, it is very easy to deadlock phenomenon, and then when debugging will often go from one trigger to another, the continuous traceability of the relationship, it is easy to make head big. In fact, in terms of performance, the trigger does not improve the performance, but from the code, it may be easy to implement the business in coding, so my view is: Discard triggers. The function of a trigger can be implemented by a stored procedure basically.

2, the advantages of a lot of stored procedures, you can often use

You can encapsulate data logic and business rules so that users can access data and objects only through the way that developers and database administrators intend to use them. Validation of parameterized stored procedures for all user input can be used to prevent SQL injection attacks. If you are using dynamic SQL, be sure to parameterize the command and never include the parameter values directly in the query string. Ad hoc queries and data modifications can be prohibited. This prevents users from maliciously or unintentionally damaging data or executing queries to avoid reducing the performance of the server or network. You can handle errors in procedure code without having to pass the error directly to the client application. This prevents error messages from being returned to prevent them from potentially helping to detect attacks. Logs an error on the server and processes it. Stored procedures can be written only once and can be accessed by many applications. The client application does not need to know any information about the underlying data structure. As long as changes do not affect the parameter list or the returned data type, you can change the stored procedure code without having to make changes in the client application. Stored procedures can reduce network traffic by combining multiple operations into a procedure call. Good security-the ability to access execute stored procedures without having to have direct manipulation of the underlying table reduces network traffic-stored procedures can contain multiple SQL statements, but only one statement to execute the stored procedure, thus reducing the number of calls to the server and the length of the client application fast execution- Stored procedures are syntactically checked and compiled for the first time, compiled versions are stored in the cache, and used again to ensure consistency-if users modify the data only through stored procedures, you can eliminate the problems caused by accidental modifications to reduce the errors of operators and programmers-because less information is delivered, Therefore, it is easier to perform complex tasks and is not prone to SQL errors

        3, considering portability, fatal to stored procedures         If a system uses stored procedures too much, the system's business logic relies too much on the database, This gives the system an extra layer of business logic in a tier of databases, if you use SQL Server when you develop, and then find that the data is too large, you need to improve performance porting to Oracle or MySQL, this can be cumbersome, the equivalent of rewriting the stored procedure, which is intolerable. We usually do in the project, often a function in the client to achieve a very laborious, in the server can easily be achieved, so many people will choose to do in the service, but for later leave hidden dangers. When analyzing the requirements of a project, be sure to consider performance issues, how long it is possible to upgrade, if the amount of data is small, for decades with SQL Server is no problem, you can use stored procedures, but the amount of data may gradually accumulate into thousands or even more, you have to consider the portability of the system, This time try not to use stored procedures, all code control.         4, code reusability of stored procedures is poor.         object-oriented Thinking in stored procedures this is useless, two very similar functions in the need for two of stored procedures, because they are independent, can invoke each other, but can not inherit the object-oriented operations, which also increased the amount of code.
        : In a general small system (logic simple), stored procedures and triggers can be used, after all, Ms Design, can greatly improve performance; In complex systems, it is recommended that you do not use a trigger, less stored procedures.