Integrity and Security:Triggers

Triggers

A trigger is a statement that the system executes automatically as a side effect of a modification to the database. To design a trigger mechanism, we must meet two requirements:

1. Specify when a trigger is to be executed. This is broken up into an event that causes the trigger to be checked and a condition that must be satisfied for trigger execution to proceed.

2. Specify the actions to be taken when the trigger executes.

The above model of triggers is referred to as the event-condition-action model for triggers.

The database stores triggers just as if they were regular data, so that they are persistent and are accessible to all database operations. Once we enter a trigger into the database, the database system takes on the responsibility of executing it whenever the specified event occurs and the corresponding condition is satisfied.

Need for Triggers

Triggers are useful mechanisms for alerting humans or for starting certain tasks automatically when certain conditions are met. As an illustration, suppose that, instead of allowing negative account balances, the bank deals with overdrafts by setting the account balance to zero, and creating a loan in the amount of the overdraft. The bank gives this loan a loan number identical to the account number of the overdrawn ac- count. For this example, the condition for executing the trigger is an update to the ac- count relation that results in a negative balance value. Suppose that Jones’ withdrawal of some money from an account made the account balance negative. Let t denote the account tuple with a negative balance value. The actions to be taken are:

As another example of the use of triggers, suppose a warehouse wishes to maintain a minimum inventory of each item; when the inventory level of an item falls below the minimum level, an order should be placed automatically. This is how the business rule can be implemented by triggers: On an update of the inventory level of an item, the trigger should compare the level with the minimum inventory level for the item, and if the level is at or below the minimum, a new order is added to an orders relation.

Note that trigger systems cannot usually perform updates outside the database, and hence in the inventory replenishment example, we cannot use a trigger to directly place an order in the external world. Instead, we add an order to the orders relation as in the inventory example. We must create a separate permanently running system process that periodically scans the orders relation and places orders. This sys- tem process would also note which tuples in the orders relation have been processed and when each order was placed. The process would also track deliveries of orders, and alert managers in case of exceptional conditions such as delays in deliveries.

Triggers in SQL

SQL-based database systems use triggers widely, although before SQL:1999 they were not part of the SQL standard. Unfortunately, each database system implemented its

own syntax for triggers, leading to incompatibilities. We outline in Figure 6.3 the SQL:1999 syntax for triggers (which is similar to the syntax in the IBM DB2 and Oracle database systems).

This trigger definition specifies that the trigger is initiated after any update of the relation account is executed. An SQL update statement could update multiple tuples of the relation, and the for each row clause in the trigger code would then explicitly iterate over each updated row. The referencing new row as clause creates a variable nrow (called a transition variable), which stores the value of an updated row after the update.

The when statement specifies a condition, namely nrow.balance < 0. The system executes the rest of the trigger body only for tuples that satisfy the condition. The begin atomic ... end clause serves to collect multiple SQL statements into a single compound statement. The two insert statements with the begin ... end structure carry out the specific tasks of creating new tuples in the borrower and loan relations to represent the new loan. The update statement serves to set the account balance back to 0 from its earlier negative value.

The triggering event and actions can take many forms:

• The triggering event can be insert or delete, instead of update.

For example, the action on delete of an account could be to check if the holders of the account have any remaining accounts, and if they do not, to delete them from the depositor relation. You can define this trigger as an exercise (Exercise 6.7).

As another example, if a new depositor is inserted, the triggered action could be to send a welcome letter to the depositor. Obviously a trigger cannot directly cause such an action outside the database, but could instead add a tuple to a relation storing addresses to which welcome letters need to be sent. A separate process would go over this table, and print out letters to be sent.

• For updates, the trigger can specify columns whose update causes the trigger to execute. For instance if the first line of the overdraft trigger were replaced by

create trigger overdraft-trigger after update of balance on account

then the trigger would be executed only on updates to balance; updates to other attributes would not cause it to be executed.

• The referencing old row as clause can be used to create a variable storing the old value of an updated or deleted row. The referencing new row as clause can be used with inserts in addition to updates.

• Triggers can be activated before the event (insert/delete/update) instead of after the event.

Such triggers can serve as extra constraints that can prevent invalid updates. For instance, if we wish not to permit overdrafts, we can create a before trigger that rolls back the transaction if the new balance is negative.

As another example, suppose the value in a phone number field of an inserted tuple is blank, which indicates absence of a phone number. We can define a trigger that replaces the value by the null value. The set statement can be used to carry out such modifications.

• Instead of carrying out an action for each affected row, we can carry out a single action for the entire SQL statement that caused the insert/delete/update. To do so, we use the for each statement clause instead of the for each row clause.

The clauses referencing old table as or referencing new table as can then be used to refer to temporary tables (called transition tables) containing all the affected rows. Transition tables cannot be used with before triggers, but can be used with after triggers, regardless of whether they are statement triggers or row triggers.

A single SQL statement can then be used to carry out multiple actions on the basis of the transition tables.

Returning to our warehouse inventory example, suppose we have the following relations:

• inventory(item, level), which notes the current amount (number/weight/volume) of the item in the warehouse

• minlevel(item, level), which notes the minimum amount of the item to be maintained

• reorder(item, amount), which notes the amount of the item to be ordered when its level falls below the minimum

• orders(item, amount), which notes the amount of the item to be ordered.

We can then use the trigger shown in Figure 6.4 for reordering the item.

Note that we have been careful to place an order only when the amount falls from above the minimum level to below the minimum level. If we only check that the new value after an update is below the minimum level, we may place an order erroneously when the item has already been reordered.

Many database systems provide nonstandard trigger implementations, or implement only some of the trigger features. For instance, many database systems do not implement the before clause, and the keyword on is used instead of after. They may not implement the referencing clause. Instead, they may specify transition tables by using the keywords inserted or deleted. Figure 6.5 illustrates how the overdraft trigger would be written in MS-SQLServer. Read the user manual for the database system you use for more information about the trigger features it supports.

When Not to Use Triggers

There are many good uses for triggers, such as those we have just seen in Section 6.4.2, but some uses are best handled by alternative techniques. For example, in the past, system designers used triggers to maintain summary data. For instance, they used triggers on insert/delete/update of a employee relation containing salary and dept at- tributes to maintain the total salary of each department. However, many database systems today support materialized views (see Section 3.5.1), which provide a much

easier way to maintain summary data. Designers also used triggers extensively for replicating databases; they used triggers on insert/delete/update of each relation to record the changes in relations called change or delta relations. A separate process copied over the changes to the replica (copy) of the database, and the system executed the changes on the replica. Modern database systems, however, provide built-in facilities for database replication, making triggers unnecessary for replication in most cases.

In fact, many trigger applications, including our example overdraft trigger, can be substituted by “encapsulation” features being introduced in SQL:1999. Encapsulation can be used to ensure that updates to the balance attribute of account are done only through a special procedure. That procedure would in turn check for negative bal- ance, and carry out the actions of the overdraft trigger. Encapsulations can replace the reorder trigger in a similar manner.

Triggers should be written with great care, since a trigger error detected at run time causes the failure of the insert/delete/update statement that set off the trigger. Furthermore, the action of one trigger can set off another trigger. In the worst case, this could even lead to an infinite chain of triggering. For example, suppose an insert trigger on a relation has an action that causes another (new) insert on the same relation. The insert action then triggers yet another insert action, and so on ad infinitum. Database systems typically limit the length of such chains of triggers (for example to 16 or 32), and consider longer chains of triggering an error.

Triggers are occasionally called rules, or active rules, but should not be confused with Data log rules (see Section 5.2), which are really view definitions.