a statement-level INSTEAD OF trigger fires, then no row-level triggers fire as a result of that
statement. However, the body of the statement-level trigger could perform other operations
that, in turn, cause other row-level triggers to fire.
If you are defining an INSTEAD OF trigger, then you cannot use the UPDATE OF
column-list
clause, the ORDER clause, or the WHEN clause.
ORDER clause – When defining additional triggers of the same type (insert, update, or
delete) to fire at the same time (before, after, or resolve), you must specify an ORDER clause
to tell the database server the order in which to fire the triggers. Order numbers must be unique
among same-type triggers configured to fire at the same time. If you specify an order number
that is not unique, then an error is returned. Order numbers do not need to be in consecutive
order (for example, you could specify 1, 12, 30). The database server fires the triggers starting
with the lowest number.
If you omit the ORDER clause, or specify 0, then the database server assigns the order of 1.
However, if another same-type trigger is already set to 1, then an error is returned.
When adding additional triggers, you may need to modify the existing same-type triggers for
the event, depending on whether the actions of the triggers interact. If they do not interact, then
the new trigger must have an ORDER value higher than the existing triggers. If they do
interact, you need to consider what the other triggers do, and you may need to change the order
in which they fire.
The ORDER clause is not supported for INSTEAD OF triggers since there can only be one
INSTEAD OF trigger of each type (insert, update, or delete) defined on a table or view.
REFERENCING clause – The REFERENCING OLD and REFERENCING NEW clauses
allow you to refer to the inserted, deleted, or updated rows. With this clause an UPDATE is
treated as a delete followed by an insert.
An INSERT takes the REFERENCING NEW clause, which represents the inserted row.
There is no REFERENCING OLD clause.
A DELETE takes the REFERENCING OLD clause, which represents the deleted row. There
is no REFERENCING NEW clause.
An UPDATE takes the REFERENCING OLD clause, which represents the row before the
update, and it takes the REFERENCING NEW clause, which represents the row after the
update.
The meanings of REFERENCING OLD and REFERENCING NEW differ, depending on
whether the trigger is a row-level or a statement-level trigger. For row-level triggers, the
REFERENCING OLD clause allows you to refer to the values in a row before an update or
delete, and the REFERENCING NEW clause allows you to refer to the inserted or updated
values. The OLD and NEW rows can be referenced in BEFORE and AFTER triggers. The
REFERENCING NEW clause allows you to modify the new row in a BEFORE trigger before
the insert or update operation takes place.
SQL Statements
226 SAP Sybase IQ