Stored Procedures and Triggers and the Object-Oriented Project

Client/server server technology really began to take shape in the late 1980s and early 1990s. One technology that boosted its credibility was the stored procedure and trigger. Initially offered by Sybase in its SQL Server product, the technology today is supported by most major database vendors.

Stored procedures and triggers gained wide acceptance for several reasons:

• They provide for the precompilation of SQL and procedural programming logic (if...then...else) into a tightly bound database object that can be invoked from a client or another server that is acting as a client to yet another server.

• They provide performance advantages over dynamic SQL (i.e., SQL that originates at the client and is then passed across the network). Dynamic SQL must be parsed, validated, and optimized before execution, whereas stored procedures and triggers wait to be invoked in an executable state.

• They provide a common, central repository for data access, thereby reducing redundant SQL code that might spread across multiple client sources.

Despite the promise of stored procedures and triggers, they fly in the face of object-oriented principles and concepts—for example, as follows:

• They are written in DBMS-dependent languages, such as SQL Server's Transact-SQL or Oracle's PL/SQL, which are not transportable across database platforms. (However, this is destined to change because most database vendors have announced support of Java as their stored procedure language.)

• They contain business rules specific to a given application and tie the rules not only to a specific DBMS language but also to the data access language (SQL).

This technology has both opponents and proponents. Developers who want an extensible design that is portable across database platforms oppose it. Proponents argue that if a stored procedure or trigger can make a poorly performing transaction really fast, then it doesn't matter what is used to meet the service-level agreement with one's clients.

I use stored procedures and triggers as the ultimate screwdriver to fine-tune the application. I initially approach my design without them. Then if I experience problems with performance, I use a stored procedure to perform the typical create, read, update, delete (CRUD) activities in SQL (i.e., insert, select, update, and delete). As a last resort, if a complicated business rule that requires access to many rows of information is simply not performing, I use a stored procedure or trigger. Keep in mind, however, that this migration from a program code solution to a database code solution is not trivial and will take some time.

With the advent of the thin-client computing model, most, if not all, of the business logic is being moved to the server. This makes not using a stored procedure more acceptable because the server that houses the business rules will probably be located in the same computing complex as the machine that houses the database. Furthermore, the network speeds that these machines can achieve are very fast, approaching gigabit throughput. These changes would remove many of the reasons for using stored procedures and triggers in the first place.

The key point is that the option exists. The bottom line in the decision process is meeting the performance goals of the application. Show me the last systems designer who was able to get away with ten-minute response times by saying, “We did it for object purity.” (Don't laugh—I actually heard that line used once.)