Databases Reference
In-Depth Information
When we define a trigger, it is better to avoid execution when not needed. In our example,
the trigger has to be executed only when the fields
PAYMENT
and
NUMBER_PAYMENTS
are
updated. So, we can define the trigger using an
OF
clause to identify the updated columns,
which fire the trigger execution, as shown in the following code:
CREATE OR REPLACE TRIGGER TR_LOANS_INS
BEFORE UPDATE OR INSERT
OF PAYMENT, NUMBER_PAYMENTS
ON sh.LOANS
FOR EACH ROW
Using the highlighted
OF
clause, we have declared that the trigger needs to be executed
only if the fields
PAYMENT
and
NUMBER_PAYMENTS
are updated, avoiding unnecessary
code execution when updating other fields (in our
LOANS
table, there are few fields to
provide a simple test environment; in real world scenarios a similar table would contain
a lot more information).
Another improvement in this direction is the use of the
WHEN
clause, which limits the
execution of the trigger only when certain conditions are met. For example, we can write
a trigger as follows:
CREATE OR REPLACE TRIGGER TR_LOANS_INS
BEFORE UPDATE OR INSERT
OF PAYMENT, NUMBER_PAYMENTS
ON sh.LOANS
FOR EACH ROW
WHEN ((new.PAYMENT > 0) AND (new.NUMBER_PAYMENTS > 0))
In the
WHEN
clause (the last line in the previous code excerpt), we have defined two
conditions to be met to fire the trigger, namely, the new values for the fields
PAYMENT
and
NUMBER_PAYMENTS
have to be greater than zero.
If we use the
WHEN
clause in trigger declaration, the specified
conditions are tested for each row and the trigger body is
executed only for the rows that match these conditions.
