Database Reference
In-Depth Information
Triggers Versus Stored Procedures
Trigger
Module of code that is called by the DBMS when INSERT, UPDATE,
or DELETE commands are issued.
Assigned to a table or view.
Depending on the DBMS, may have more than one trigger per table or view.
Triggers may issue INSERT, UPDATE, and DELETE commands and
thereby may cause the invocation of other triggers.
Stored Procedure
Module of code that is called by a user or database administrator.
Assigned to a database, but not to a table or a view.
Can issue INSERT, UPDATE, DELETE, and MERGE commands.
Figure 7-30
triggers Versus Stored
Procedures
Used for repetitive administration tasks or as part of an application.
Advantages of Stored Procedures
The advantages of using stored procedures are listed in Figure 7-31. Unlike application code, stored
procedures are never distributed to client computers. They always reside in the database and are
processed by the DBMS on the database server. Thus, they are more secure than distributed appli-
cation code, and they also reduce network traffic. Increasingly, stored procedures are the preferred
mode of processing application logic over the Internet or corporate intranets. Another advantage
of stored procedures is that their SQL statements can be optimized by the DBMS compiler.
When application logic is placed in a stored procedure, many different application program-
mers can use that code. This sharing results not only in less work, but also in standardized pro-
cessing. Further, the developers best suited for database work can create the stored procedures
while other developers, say, those who specialize in Web-tier programming, can do other work.
Because of these advantages, it is likely that stored procedures will see increased use in the future.
The WORK_AddWorkTransaction Stored Procedure
Figure 7-32 shows a stored procedure that records the acquisition of a work in the View Ridge
Gallery database. Again, this code is generic, but the code style in Figure 7-32 is closer to that
Figure 7-31
advantages of Stored
Procedures
Advantages of Stored Procedures
Greater security.
Decreased network traffic.
SQL can be optimized.
Code sharing.
Less work.
Standardized processing.
Specialization among developers.
 
Search WWH ::




Custom Search