As described at the beginning of this chapter, a trigger is a special set of stored procedure code whose activation is caused by modifications to the database contents. Unlike stored procedures created with a CREATE PROCEDURE statement, a trigger is not activated by a CALL or EXECUTE statement. Instead, the trigger is associated with a database table. When the data in the table is changed (by an INSERT, DELETE, or UPDATE statement), the trigger is fired, which means that the DBMS executes the SQL statements that make up the body of the trigger.
Triggers can be used to cause automatic updates of information within a database. For example, suppose you wanted to set up the sample database so that any time a new salesperson is inserted into the SALESREPS table, the sales target for the office where the salesperson works is raised by the new salesperson’s quota. Here is an Oracle PL/SQL trigger that accomplishes this goal:
create trigger upd_tgt
/* Insert trigger for SALESREPS */
before insert on salesreps
for each row
when (new.quota is not null)
begin
update offices
set target = target + new.quota;
end;
The CREATE TRIGGER statement is used by most DBMS brands that support triggers to define a new trigger within the database. It assigns a name to the trigger (UPD_TGT for this one) and identifies the table the trigger is associated with (SALESREPS) and the update action(s) on that table that will cause the trigger to be executed (INSERT in this case). The body of this trigger tells the DBMS that for each new row inserted into the table, it should execute the specified UPDATE statement for the OFFICES table. The QUOTA value from the newly inserted SALESREPS row is referred to as NEW.QUOTA within the trigger body.
1. Advantages and Disadvantages of Triggers
Triggers can be extremely useful as an integral part of a database definition, and they can be used for a variety of different functions, including these:
- Auditing changes. A trigger can detect and disallow specific updates and changes that should not be permitted in the database.
- Cascaded operations. A trigger can detect an operation within the database (such as deletion of a customer or salesperson) and automatically cascade the impact throughout the database (such as adjusting account balances or sales targets).
- Enforce interrelationships. A trigger can enforce more complex interrelationships among the data in a database than those that can be expressed by simple referential integrity constraints or check constraints, such as those that require a sequence of SQL statements or IF…THEN…ELSE processing.
- Stored procedure invocation. A trigger can call one or more stored procedures or even invoke actions outside the DBMS itself through external procedure calls in response to database updates.
In each of these cases, a trigger embodies a set of business rules that govern the data in the database and modifications to that data. The rules are embedded in a single place in the database (the trigger definition). As a result, they are uniformly enforced across all applications that access the database. When they need to be changed, they can be changed once with the assurance that the change will be applied uniformly.
The major disadvantage of triggers is their potential performance impact. If a trigger is set on a particular table, then every database operation that attempts to update that table causes the DBMS to execute the trigger procedure. For a database that requires very high data insertion or update rates, the overhead of this processing can be considerable. This is especially true for bulk load operations, where the data may have already been prechecked for integrity. To deal with this disadvantage, some DBMS brands allow triggers to be selectively enabled and disabled, as appropriate.
2. Triggers in Transact-SQL
Transact-SQL provides triggers through a CREATE TRIGGER statement in both its Microsoft SQL Server and Sybase Adaptive Server dialects. Here is a Transact-SQL trigger definition for the sample database, which implements the same trigger function as the preceding Oracle PL/SQL example:
create trigger upd_tgt
/* Insert trigger for SALESREPS */
on salesreps
for insert
as
if (@@rowcount = 1)
begin
update offices
set target = target + inserted.quota
from offices, inserted
where offices.office = inserted.rep_office;
end
else
raiserror 23456
The first clause names the trigger (UPD_TGT). The second clause is required and identifies the table to which the trigger applies. The third clause is also required and tells which database update operations cause the trigger to be fired. In this case, only an INSERT statement causes the trigger to fire. You can also specify UPDATE or DELETE operations, or a combination of two or three of these operations in a comma-separated list. Transact-SQL restricts triggers so that only one trigger may be defined on a particular table for each of the three data modification operations. The body of the trigger follows the AS keyword. To understand the body of a trigger like this one, you need to understand how Transact-SQL treats the rows in the target table during database modification operations.
For purposes of trigger operation, Transact-SQL defines two logical tables whose column structure is identical to the target table on which the trigger is defined. One of these logical tables is named DELETED, and the other is named INSERTED. These logical tables are populated with rows from the target table, depending on the data modification statement that caused the trigger to fire, as follows:
- DELETE. Each target table row that is deleted by the DELETE statement is placed into the DELETED table. The INSERTED table is empty.
- INSERT. Each target table row that is added by the INSERT statement is also placed into the INSERTED table. The DELETED table is empty.
- UPDATE. For each target table row that is changed by the UPDATE statement, a copy of the row before any modifications is placed into the DELETED table. A copy of the row after all modifications is placed into the INSERTED table.
These two logical tables can be referenced within the body of the trigger, and the data in them can be combined with data from other tables during the trigger’s operation. In this Transact-SQL trigger, the trigger body first tests to make sure that only a single row of the SALESREPS table has been inserted, by checking the system variable @@ROWCOUNT. If this is true, then the QUOTA column from the INSERTED logical table is added to the appropriate row of the OFFICES table. The appropriate row is determined by joining the logical table to the OFFICES table based on matching office numbers.
Here is a different trigger that detects a different type of data integrity problem.
In this case, it checks for an attempt to delete a customer when there are still orders outstanding in the database for that customer. If it detects this situation, the trigger automatically rolls back the entire transaction, including the DELETE statement that fired the trigger:
create trigger chk_del_cust
/* Delete trigger for CUSTOMERS */
on customers
for delete
as
/* Detect any orders for deleted cust #’s */
if (select count(*)
from orders, deleted
where orders.cust = deleted.cust_num) > 0
begin
rollback transaction
print “Cannot delete; still have orders”
raiserror 31234
end
Transact-SQL triggers can be specified to fire on any UPDATE for a target table, or just for updates of selected columns. This trigger fires on inserts or updates to the SALESREPS table and does different processing depending on whether the QUOTA or SALES column has been updated:
create trigger upd_reps
/* Update trigger for SALESREPS */
on salesreps
for insert, update
if update(quota)
/* Handle updates to quota column */
…
if update (sales)
/* Handle updates to sales column */
…
3. Triggers in Informix SPL
Informix also supports triggers through a CREATE TRIGGER statement. As in the Transact-SQL dialect, the beginning of the CREATE TRIGGER statement defines the trigger name, the table on which the trigger is being defined, and the triggering actions. Here are statement fragments that show the syntax:
create trigger new_sls
insert on salesreps . . .
create trigger del_cus_chk
delete on customers …
create trigger ord_upd
update on orders …
create trigger sls_upd
update of quota, sales on salesreps …
The last example is a trigger that fires only when two specific columns of the SALESREPS table are updated.
Informix allows you to specify that a trigger should operate at three distinct times during the processing of a triggered change to the target table:
- BEFORE. The trigger fires before any changes take place. No rows of the target table have yet been modified.
- AFTER. The trigger fires after all changes take place. All affected rows of the target table have been modified.
- FOR EACH ROW. The trigger fires repeatedly, once as each row affected by the change is being modified. Both the old and new data values for the row are available to the trigger.
An individual trigger definition can specify actions to be taken at one or more of these steps. For example, a trigger could execute a stored procedure to calculate the sum of all orders BEFORE an update, monitor updates to each ORDERS row as they occur with a second action, and then calculate the revised order total AFTER the update with a call to another stored procedure. Here is a trigger definition that does all of this:
create trigger upd_ord
update of amount on orders referencing old as pre new as post
/* Calculate order total before changes */
before (execute procedure add_orders()
into old_total;)
/* Capture order increases and decreases */
for each row
when (post.amount < pre.amount)
/* Write decrease data into table */
(insert into ord_less
values (pre.cust,
pre.order_date,
pre.amount,
post.amount);)
when (post.amount > pre.amount)
/* Write increase data into table */
(insert into ord_more
values (pre.cust,
pre.order_date,
pre.amount,
post.amount);)
/* After changes, recalculate total */
after (execute procedure add_orders()
into new_total;)
The BEFORE clause in this trigger specifies that a stored procedure named ADD_ORDERS is to be called before any UPDATE statement processing occurs. Presumably, this procedure calculates the total orders and returns the total value into the local variable OLD_TOTAL. Similarly, the AFTER clause specifies that a stored procedure (in this case, the same one) is to be called after all UPDATE statement processing is complete. This time, the total orders amount is placed into a different local variable, NEW_TOTAL.
The FOR EACH ROW clause specifies the action to be taken as each affected row is updated. In this case, the requested action is an INSERT into one of two order tracking tables, depending on whether the order amount is being increased or decreased. These tracking tables contain the customer number, date, and both the old and new order amounts. To obtain the required values, the trigger must be able to refer to both the old (prechange) and the new (postchange) values of each row.
The REFERENCING clause provides names by which these two states of the currently- being-modified row of the ORDERS table can be used. In this example, the prechange values of the columns are available through the column name qualifier PRE, and the postchange values through the column name qualifier POST. These are not special names; any names can be used.
Informix is more limited than some other DBMS brands in the actions that can be specified within the trigger definition itself. These actions are available:
- An INSERT statement
- A DELETE statement
- An UPDATE statement
- An EXECUTE PROCEDURE statement
In practice, the last option provides quite a bit of flexibility. The called procedure can perform almost any processing that could be done inline within the trigger body itself.
4. Triggers in Oracle PL/SQL
Oracle provides a more complex trigger facility than either the Informix or Transact- SQL facility described in the preceding sections. It uses a CREATE TRIGGER statement to specify triggered actions. As in the Informix facility, a trigger can be specified to fire at specific times during specific update operations:
- Statement-level trigger. A statement-level trigger fires once for each data modification statement. It can be specified to fire either before the statement is executed or after the statement has completed its action.
- Row-level trigger. A row-level trigger fires once for each row being modified by a statement. In Oracle’s structure, this type of trigger may also fire either before the row is modified or after it is modified.
- Instead-of trigger. An instead-of trigger takes the place of an attempted data modification statement. It provides a way to detect an attempted UPDATE, INSERT, or DELETE operation by a user or procedure, and substitute other processing instead. You can specify that a trigger should be executed instead of a statement, or that it should be executed instead of each attempted modification of a row.
create trigger bef_upd_ord
before update on
orders begin
/* Calculate order total before changes */
old_total = add_orders();
end;
create trigger aft_upd_ord
after update on orders
begin
/* Calculate order total after changes */
new_total = add_orders();
end;
create trigger dur_upd_ord
before update of amount on orders
referencing old as pre new as post
/* Capture order increases and decreases */
for each row
when (post.amount != pre.amount)
begin
if (post.amount < pre.amount)
then
/* Write decrease data into table */ insert into ord_less
values (pre.cust,
pre.order_date,
pre.amount,
post.amount);
elsif (post.amount > pre.amount)
then
/* Write increase data into table */
insert into ord_more
values (pre.cust,
pre.order_date,
pre.amount,
post.amount);
end if;
end;
These trigger structures and their options provide 14 different valid Oracle trigger types (12 resulting from a choice of INSERT/DELETE/UPDATE triggers for BEFORE or AFTER processing at the row or statement level (3x2x2), and two more from instead-of triggers at the statement or row level). In practice, relational databases built using Oracle don’t tend to use instead-of triggers; they were introduced in Oracle8 to support some of its newer object-oriented features.
The code just shown is a PL/SQL trigger definition that implements the same processing as in the complex Informix example from the previous section. It has been split into three separate Oracle CREATE TRIGGER statements; one each for the BEFORE and AFTER statement-level triggers and one trigger that is executed for each update row.
5. Other Trigger Considerations
Triggers pose some of the same issues for DBMS processing that UPDATE and DELETE rules present. For example, triggers can cause a cascaded series of actions. A user’s attempt to update a table may cause a trigger to fire. Within the body of that trigger is an UPDATE statement for another table. A trigger on that table causes the UPDATE of still another table, and so on. The situation is even worse if one of the fired triggers attempts to update the original target table that caused the firing of the trigger sequence in the first place! In this case, an infinite loop of fired triggers could result.
Various DBMS systems deal with this issue in different ways. Some impose restrictions on the actions that can be taken during execution of a trigger. Others provide built-in functions that allow a trigger’s body to detect the level of nesting at which the trigger is operating. Some provide a system setting that controls whether cascaded trigger processing is allowed. Finally, some provide a limit on the number of levels of nested triggers that can fire.
One additional issue associated with triggers is the overhead that can result during very heavy database usage, such as when bulk data is being loaded into a database. Some DBMS brands provide the ability to selectively enable and disable trigger processing to handle this situation. Oracle, for example, provides this form of the ALTER TRIGGER statement:
ALTER TRIGGER BEF_UPD_ORD DISABLE;
A similar capability is provided within the CREATE TRIGGER statement of Informix.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.