Triggers in SQL

Triggers in SQL

The SQL trigger statement gives the user a number of different options in the event, condition, and action parts. Here are the principal features.

1. The action may be executed either before or after the triggering event.

2. The action can refer to both old and/or new values of tuples that were inserted, deleted, or updated in the event that triggered the action.

3. Update events may be limited to a particular attribute or set of attributes.

4. A condition may be specified by a WHEN clause; the action is executed only if the rule is triggered and the condition holds when the triggering event occurs.

5. The programmer has an option of specifying that the action is performed either:

(a) Once for each modified tuple, or
(b) Once for all the tuples that are changed in one database operation.

Before giving the details of the syntax for triggers, let us look at an example that will show the most important syntactic as well as semantic points. In this example, the trigger executes once for each tuple that is updated.

Example 1 : We shall write an SQL trigger that applies to the

MovieExec (name, address, cert#, netWorth)

table. It is triggered by updates to the netWorth attribute. The effect of this trigger is to foil any attempt to lower the net worth of a movie executive. The trigger declaration appears in Figure 1.

An SQL trigger

Line (1) introduces the declaration with the keywords CREATE TRIGGER and the name of the trigger. Line (2) then gives the triggering event, namely the update of the netWorth attribute of the MovieExec relation. Lines (3) through (5) set up a way for the condition and action portions of this trigger to talk about both the old tuple (the tuple before the update) and the new tuple (the tuple after the update). These tuples will be referred to as OldTuple and NewTuple, according to the declarations in lines (4) and (5), respectively. In the condition and action, these names can be used as if they were tuple variables declared in the FROM clause of an ordinary SQL query.

Line (6), the phrase FOR EACH ROW, expresses the requirement that this trigger is executed once for each updated tuple. If this phrase is missing or it is replaced by the default FOR EACH STATEMENT, then the triggering would occur once for an SQL statement, no matter how many triggering-event changes to tuples it made. We would not then declare alias for old and new rows, but we might use OLD TABLE and NEW TABLE, introduced below.

Line (7) is the condition part of the trigger. It says that we only perform the action when the new net worth is lower than the old net worth; i.e., the net worth of an executive has shrunk.

Lines (8) through (10) form the action portion. This action is an ordinary SQL update statement that has the effect of restoring the net worth of the executive to what it was before the update. Note that in principle, every tuple of MovieExec is considered for update, but the WHERE-clause of line (10) guarantees that only the updated tuple (the one with the proper cert#) will be affected.

Certainly Example 1 shows only some of the features of SQL triggers. In the points that follow, we shall outline the options that are offered by triggers and how to express these options.

●  Line (2) of Figure 1 says that the action of the rule is executed after the triggering event, as indicated by the keyword AFTER. We may replace AFTER by BEFORE, in which case the WHEN condition is tested before the triggering event, that is, before the modification that awakened the trigger has been made to the database. If the condition is true, then the action of the trigger is executed. Then, the event that awakened the trigger is executed, regardless of whether the condition is true.

●  Besides UPDATE, other possible triggering events are INSERT and DELETE. The OF netWorth clause in line (2) of Figure 1 is optional for UPDATE events, and if present defines the event to be only an update of the attribute(s) listed after the keyword OF. An OF clause is not permitted for INSERT or DELETE events; these events make sense for entire tuples only.

●  The WHEN clause is optional. If it is missing, then the action is executed whenever the trigger is awakened.

●  While we showed a single SQL statement as an action, there can be any number of such statements, separated by semicolons and surrounded by BEGIN.. .END.

●  When the triggering event is an update, then there will be old and new tuples, which are the tuple before the update and after, respectively. We give these tuples names by the OLD ROW AS and NEW ROW AS clauses seen in lines (4) and (5). If the triggering event is an insertion, then we may use a NEW ROW AS clause to give a name for the inserted tuple, and OLD ROW AS is disallowed. Conversely, on a deletion OLD ROW AS is used to name the deleted tuple and NEW ROW AS is disallowed.

●  If we omit the FOR EACH ROW on line (6), then a row-level trigger such as Figure 1 becomes a statement-level trigger. A statement-level trigger is executed once whenever a statement of the appropriate type is executed, no matter how many rows - zero, one, or many - it actually affects. For example, if we update an entire table with an SQL update statement, a statement-level update trigger would execute only once, while a tuple-level trigger would execute once for each tuple to which an update is applied. In a statement-level trigger, we cannot refer to old and new tuples directly, as we did in lines (4) and (5). On the other hand, any trigger - whether row- or statement-level - can refer to the relation of old tuples (deleted tuples or old versions of updated tuples)  and the relation of new tuples (inserted tuples or new versions of updated tuples), using declarations such as OLD TABLE AS OldStuff and NEW TABLE AS NewStuff.

Example 2 : Suppose we want to prevent the average net worth of movie executives from dropping below  $500,000. This constraint could be violated by an insertion, a deletion, or an update to the netWorth column of

MovieExec (name, address, cert#, netWorth)

The subtle point is that we might, in one INSERT or UPDATE statement insert or change many tuples of MovieExec, and during the modification, the average net worth might temporarily dip below $500,000 and then rise above it by the time all the modifications are made. We only want to reject the entire set of modifications if the net worth is below $500,000 at the end of the statement.

It is necessary to write one trigger for each of these three events: insert, delete, and update of relation MovieExec. Figure 2 shows the trigger for the update event. The triggers for the insertion arid deletion of tuples are similar but slightly simpler.

Constraining the average net worth

Lines (3) through (5) declare that NewStuff arid OldStuff are the names of relations containing the new tuples and old tuples that are involved in the database operation that awakened our trigger. Note that one database statement can modify many tuples of a relation, and if such a statement executes, there can be many tuples in NewStuff and OldStuff .

If the operation is an update, then NewStuff and OldStuff are the new and old versions of the updated tuples,  respectively. If an analogous trigger were written for deletions, then the deleted tuples would be in OldStuff, and there would be no declaration of a relation name like NewStuff for NEW TABLE in this trigger. Similarly, in the analogous trigger for insertions, the new tuples would be in NewStuff, and there would be no declaration of OldStuff.

Line (6) tells us that this trigger is executed once for a statement, regardless of how many tuples are modified. Line (7) is the condition. This condition is satisfied if the average net worth after the update is less than $500,000.

The action of lines (8) through (13) consists of two statements that restore the old relation MovieExec if the condition of the WHEN clause is satisfied; i.e., the new average net worth is too low. Lines (9) and (10) remove all the new tuples, i.e., the updated versions of the tuples, while lines (11) and (12) restore the tuples as they were before the update.