Many of the data integrity issues in the real world have to do with the rules and procedures of an organization. For example, the company that is modeled by the sample database might have rules like these:
- No customer is allowed to place orders that would exceed the customer’s credit limit.
- The sales vice president must be notified whenever any customer is assigned a credit limit higher than $50,000.
- Orders may remain on the books for only six months; orders older than six months must be canceled and reentered.
In addition, there are often accounting rules that must be followed to maintain the integrity of totals, counts, and other amounts stored in a database. For the sample database, these rules probably make sense:
- Whenever a new order is taken, the SALES column for the salesperson who took the order and for the office where that salesperson works should be increased by the order amount. Deleting an order or changing the order amount should also cause the SALES columns to be adjusted.
- Whenever a new order is taken, the QTY_ON_HAND column for the product being ordered should be decreased by the quantity of products ordered. Deleting an order, changing the quantity, or changing the product ordered should also cause corresponding adjustments to the QTY_ON_HAND
These rules fall outside the realm of the SQL language as defined by the SQL1 standard and as implemented by many SQL-based DBMS products today. The DBMS takes responsibility for storing and organizing data and ensuring its basic integrity, but enforcing the business rules is the responsibility of the application programs that access the database.
Placing the burden of enforcing business rules on the application programs that access the database has several disadvantages:
- Duplication of effort. If six different programs deal with various updates to the ORDERS table, each of them must include code that enforces the rules relating to ORDERS
- Lack of consistency. If several programs written by different programmers handle updates to a table, they will probably enforce the rules somewhat differently.
- Maintenance problems. If the business rules change, the programmers must identify every program that enforces the rules, then locate the code and modify it correctly.
- Complexity. There are often many rules to remember. Even in the small sample database, a program that handles order changes must worry about enforcing credit limits, adjusting sales totals for salespeople and offices, and adjusting the quantities on hand. A program that handles simple updates can become complex very quickly.
The requirement that application programs enforce business rules is not unique to SQL. Application programs have had that responsibility since the earliest days of COBOL programs and file systems. However, there has been a steady trend over the years to put more “understanding” of the data and more responsibility for its integrity into the database itself. In 1986, the Sybase DBMS introduced the concept of a trigger as a step toward including business rules in a relational database. The concept proved to be very popular, so support for triggers began to appear in many SQL DBMS products in the early 1990s, including those of the mainstream enterprise DBMS vendors. Triggers and the enforcement of business rules that they provide have been especially useful in enterprise database environments. When hundreds of application programs are being developed or modified every year by dozens of application programmers, the ability to centralize the definition and administration of business rules can be very useful.
1. What Is a Trigger?
The concept of a trigger is relatively straightforward. For any event that causes a change in the contents of a table, a user can specify an associated action that the DBMS should carry out. The three events that can trigger an action are attempts to INSERT, DELETE, or UPDATE rows of the table. The action triggered by an event is specified by a sequence of SQL statements.
To understand how a trigger works, let’s examine a concrete example. When a new order is added to the ORDERS table, these two changes to the database should also take place:
- The SALES column for the salesperson who took the order should be increased by the amount of the order.
- The QTY_ON_HAND amount for the product being ordered should be decreased by the quantity ordered.
This Transact-SQL statement defines a SQL Server trigger, named NEWORDER, that causes these database updates to happen automatically:
CREATE TRIGGER NEWORDER
ON ORDERS
FOR INSERT
AS UPDATE SALESREPS
SET SALES = SALES + INSERTED.AMOUNT
FROM SALESREPS, INSERTED
WHERE SALESREPS.EMPL_NUM = INSERTED.REP
UPDATE PRODUCTS
SET QTY_ON_HAND = QTY_ON_HAND – INSERTED.QTY
FROM PRODUCTS, INSERTED
WHERE PRODUCTS.MFR_ID = INSERTED.MFR
AND PRODUCTS.PRODUCT_ID = INSERTED.PRODUCT
The first part of the trigger definition tells SQL Server that the trigger is to be invoked whenever an INSERT statement is attempted on the ORDERS table. The remainder of the definition (after the keyword AS) defines the action of the trigger. In this case, the action is a sequence of two UPDATE statements, one for the SALESREPS table and one for the PRODUCTS table. The row being inserted is referred to using the pseudo-table name inserted within the UPDATE statements. As the example shows, SQL Server extends the SQL language substantially to support triggers. Other extensions not shown here include IF/THEN/ELSE tests, looping, procedure calls, and even PRINT statements that display user messages.
The trigger capability, while popular in many DBMS products, is not a part of the ANSI/ISO SQL2 standard. As with other SQL features whose popularity has preceded standardization, this has led to a considerable divergence in trigger support across various DBMS brands. Some of the differences between brands are merely differences in syntax. Others reflect real differences in the underlying capability.
DB2’s trigger support provides an instructive example of the differences. Here is the same trigger definition shown previously for SQL Server, this time using the DB2 syntax:
CREATE TRIGGER NEWORDER
AFTER INSERT ON ORDERS
REFERENCING NEW AS NEW_ORD
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE SALESREPS
SET SALES = SALES + NEW_ORD.AMOUNT
WHERE SALESREPS.EMPL_NUM = NEW_ORD.REP;
UPDATE PRODUCTS
SET QTY_ON_HAND = QTY_ON_HAND – NEW_ORD.QTY
WHERE PRODUCTS.MFR_ID = NEW_ORD.MFR
AND PRODUCTS.PRODUCT_ID = NEW_ORD.PRODUCT;
END
The beginning of the trigger definition includes the same elements as the SQL Server definition, but rearranges them. It explicitly tells DB2 that the trigger is to be invoked after a new order is inserted into the database. DB2 also allows you to specify that the trigger is to be carried out before a triggering action is applied to the database contents. This doesn’t make sense in this example, because the triggering event is an INSERT operation, but it does make sense for UPDATE or DELETE operations.
The DB2 REFERENCING clause specifies a table alias (NEW_ORD) that will be used to refer to the row being inserted throughout the remainder of the trigger definition. It serves the same function as the INSERTED keyword in the SQL Server trigger. The statement references the new values in the inserted row because this is an INSERT operation trigger. For a DELETE operation trigger, the old values would be referenced. For an UPDATE operation trigger, DB2 gives you the ability to refer to both the old (pre-UPDATE) values and new (post-UPDATE) values.
BEGIN ATOMIC and END serve as brackets around the sequence of SQL statements that define the triggered action. The two searched UPDATE statements in the body of the trigger definition are straightforward modifications of their SQL Server counterparts. They follow the standard SQL syntax for searched UPDATE statements, using the table alias specified by the REFERENCING clause to identify the particular row of the SALESREPS table and the PRODUCTS table to be updated. The row being inserted is referred to using the pseudo-table name inserted within the UPDATE statements.
Here is another example of a trigger definition, this time using Informix Universal
CREATE TRIGGER NEWORDER
INSERT ON ORDERS
AFTER (EXECUTE PROCEDURE NEW_ORDER)
This trigger again specifies an action that is to take place after a new order is inserted. In this case, the multiple SQL statements that form the triggered action can’t be specified directly in the trigger definition. Instead, the triggered statements are placed into an Informix stored procedure, named NEW_ORDER, and the trigger causes the stored procedure to be executed. As this and the preceding examples show, although the core concepts of a trigger mechanism are very consistent across databases, the specifics vary a great deal. Triggers are certainly among the least portable aspects of SQL databases today.
2. Triggers and Referential Integrity
Triggers provide an alternative way to implement the referential integrity constraints provided by foreign keys and primary keys. In fact, advocates of the trigger feature point out that the trigger mechanism is more flexible than the strict referential integrity provided by the ANSI/ISO standard. For example, here is a trigger that enforces referential integrity for the OFFICES/SALESREPS relationship and displays a message when an attempted update fails:
CREATE TRIGGER REP_UPDATE
ON SALESREPS
FOR INSERT, UPDATE
AS IF ((SELECT COUNT(*)
FROM OFFICES, INSERTED
WHERE OFFICES.OFFICE = INSERTED.REP OFFICE)
BEGIN
PRINT “Invalid office number specified.
ROLLBACK TRANSACTION
END
Triggers can also be used to provide extended forms of referential integrity. For example, DB2 initially provided cascaded deletes through its CASCADE delete rule but did not support cascaded updates if a primary key value is changed. This limitation need not apply to triggers, however. The following SQL Server trigger cascades any update of the OFFICE column in the OFFICES table down into the REP_OFFICE column of the SALESREPS table:
CREATE TRIGGER CHANGE_REP_OFFICE
ON OFFICES
FOR UPDATE
AS IF UPDATE (OFFICE)
BEGIN
UPDATE SALESREPS
SET SALESREPS.REP_OFFICE = INSERTED.OFFICE
FROM SALESREPS, INSERTED, DELETED
WHERE SALESREPS.REP_OFFICE = DELETED.OFFICE
END
As in the previous SQL Server example, the references DELETED.OFFICE and INSERTED.OFFICE in the trigger refer, respectively, to the values of the OFFICE column before and after the UPDATE statement. The trigger definition must be able to differentiate between these before and after values to perform the appropriate search and update actions specified by the trigger.
3. Trigger Advantages and Disadvantages
Over the last several years, the trigger mechanisms in many commercial DBMS products have expanded significantly. In many commercial implementations, the distinctions between triggers and stored procedures (described in Chapter 20) have blurred, so the action triggered by a single database change may be defined by hundreds of lines of stored procedure programming. The role of triggers has thus evolved beyond the enforcement of data integrity into a programming capability within the database.
A complete discussion of triggers is beyond the scope of this book, but even these simple examples show the power of the trigger mechanism. The major advantage of triggers is that business rules can be stored in the database and enforced consistently with each update to the database. This can dramatically reduce the complexity of application programs that access the database. Triggers also have some disadvantages, including these:
- Database complexity. When the rules are moved into the database, setting up the database becomes a more complex task. Users who could reasonably be expected to create small ad hoc applications with SQL will find that the programming logic of triggers makes the task much more difficult.
- Hidden rules. With the rules hidden away inside the database, programs that appear to perform straightforward database updates may, in fact, generate an enormous amount of database activity. The programmer no longer has total control over what happens to the database. Instead, a program-initiated database action may cause other, hidden actions.
- Hidden performance implications. With triggers stored inside the database, the consequences of executing a SQL statement are no longer completely visible to the programmer. In particular, an apparently simple SQL statement could, in concept, trigger a process that involves a sequential scan of a very large database table, which would take a long time to complete. These performance implications of any given SQL statement are invisible to the programmer.
4. Triggers and the SQL Standard
Triggers were one of the most widely praised and publicized features of Sybase SQL Server when it was first introduced, and they have since found their way into many commercial SQL products. Although the SQL2 standard provided an opportunity to standardize the DBMS implementation of triggers, the standards committee included check constraints instead. As the trigger and check-constraint examples in the preceding sections show, check constraints can be effectively used to limit the data that can be added to a table or modified in a table. However, unlike triggers, they lack the ability to cause an independent action in the database, such as adding a row or changing a data item in another table.
The extra capability provided by triggers has led several industry experts to advocate that they be included in a future SQL3 standard. Other experts have argued that triggers are a pollution of the data management function of a database, and that the functions performed by triggers belong in fourth generation languages (4GLs) and other database tools, rather than in the DBMS itself. While the debate continues, DBMS products have experimented with new trigger capabilities that extend beyond the database itself. These extended trigger capabilities allow modifications to data in a database to automatically cause actions such as sending mail, alerting a user, or launching another program to perform a task. This makes triggers even more useful and will add to the debate over including them in future official SQL standards. Regardless of the official stance, triggers have become a more and more important part of the SQL language in enterprise applications over the last several years.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.