Instead-Of Triggers

Instead-Of Triggers

There is a useful feature of triggers that did not make the SQL-99 standard, but figured into the discussion of the standard and is supported by some commercial systems. This extension allows BEFORE or AFTER to be replaced by INSTEAD OF; the meaning is that when an event awakens a trigger, the action of the trigger is done instead of the event itself.

This capability offers little when the trigger is on a stored table, but it is very powerful when used on a view. The reason is that we cannot really modify a view (see "Modifying Views"). An instead-of trigger intercepts attempts to modify the view and in its place performs whatever action the database designer deems appropriate. The following is a typical example.

Example 1 : Let us recall the definition of the view of all movies owned by Paramount:

from "View Definitions" Example 1. As we discussed in "Modifying Views" Example 1, this view is updatable, but it has the unexpected flaw that  when you insert a tuple into ParamountMovie, the system cannot deduce that the studioName attribute is surely Paramount, so that attribute is NULL in the inserted Movie tuple.

A better result can be obtained if we create an instead-of trigger on this view, as shown in Figure 1. Much of the trigger is unsurprising. We see the keyword INSTEAD OF on line (2), establishing that an attempt to insert into ParamountMovie will never take place.

Rather, we see in lines (5) and (6) the action that replaces the attempted insertion. There is an insertion into Movie, and it specifies the three attributes that we know about. Attributes title and year come from the tuple we tried to insert into the view; we refer to these values by the tuple variable NewRow that was declared in line (3) to represent the tuple we are trying to insert. The

Trigger to replace an insertion on a view by an insertion on the underlying base table

value of attribute studioName is the constant 'Paramount'. This value is not part of the inserted tuple. Rather, we assume it is the correct studio for the inserted movie, because the insertion came through the view ParamountMovie.