Triggers

TRIGGERS

a trigger defines an action to be taken when a given update (insert, update or delete) is performed on a table
triggers can be used to:

enforce complex integrity constraints that can't be accomplished with a CHECK clause; for example:

date related constraints
cross table checks
checks that involve calculations - for example, a company may have a rule that employees can't work more than x hours per week

enforce business rules

generate a re-order notice when quantity on hand falls below the re-order point

automatically update calculated fields
collect audit data

once a trigger is defined, it is "fired" automatically if the triggering event occurs
could cause a cascade of events in the database, hitting many tables

CREATE TRIGGER Statement:

BEFORE | AFTER

this clause specifies when the trigger is fired - either before the update or after the update

DELETE | INSERT | UPDATE [OF column-name]

defines the update that causes the trigger to fire
if the OF column-name clause is omitted on an UPDATE trigger, the trigger is fired on an update of any column
can specify more than one column; separate with commas

ON table-name

identifies the table

FOR EACH { ROW | STATEMENT }

if ROW is specified, the trigger is fired once for each row that is updated
if STATEMENT is specified, the trigger is fired only once, regardless of the number of rows that are updated

WHEN (condition)

can be used to specify a condition that must exist before the trigger is fired
this clause can be omitted; the condition can be coded in the PL/SQL block

Using :old and :new In Row Level Triggers

the PL/SQL statements in a trigger can access two "pseudo-records" - :new and :old
both refer to the row being updated
:old refers to the values before the update
:new refers to the values after the update
use these names to qualify column names; for example:

:new.portfoliono

would refer to the value of portfolio number in the row being updated after the update has been applied

EXAMPLE 1:

For this trigger, if the cash in a portfolio falls below a certain level, a row is generated in a table called: CASH_NOTICE

The CREATE TABLE for the CASH_NOTICE table is given below:

create table cash_notice
(portfoliono char(4) , gen_timestamp date, managerno char(2),
cash number(8,2)); The CASH_WARNING trigger generates a row in CASH_NOTICE when the cash falls below $5000: create or replace trigger cash_warning
after update of cash on portfolio
for each row
when (new.cash < 5000)
begin
insert into cash_notice
values (:new.portfoliono, sysdate, :new.managerno, :new.cash);
end;
/

EXAMPLE 2:

For this trigger, if a row is added to the HOLDING table, the cash in the corresponding PORTFOLIO row is checked to ensure there is sufficient cash to cover the stock purchase amount:
create or replace trigger check_cash
   after insert on holding
   for each row
   declare
       tempcash number(8,2);
   begin
         select cash into tempcash
            from portfolio
            where portfoliono = :new.portfoliono;
         if :new.numbershares * :new.purchaseprice > tempcash
              then raise_application_error(-20001,'Insufficient cash in portfolio');
         end if;
    end;
/

EXAMPLE 3:

For this trigger, when a row is added to the HOLDING table, the purchase price is set to the current price in the STOCK table and the cash in the corresponding portfolio is reduced: create or replace trigger set_price
    before insert on holding
    for each row
    begin
        select currentprice into :new.purchaseprice
           from stock
           where stocksymbol = :new.stocksymbol;
        update portfolio
            set cash = cash - :new.numbershares * :new.purchaseprice
            where portfoliono = :new.portfoliono;
    end;
/

since this is a BEFORE trigger, it will fire before CHECK_CASH which is an AFTER trigger
if the trigger CASH_WARNING has also been created, an insert on the HOLDING table may cause it to fire since SET-PRICE causes an update on the PORTFOLIO table
if there are several BEFORE (or AFTER) triggers for the same update, the order of their execution can not be specified; to ensure a certain sequence, all actions would need to be coded in the same trigger

The RAISE_APPLICATION_ERROR Procedure

The RAISE_APPLICATION_ERROR procedure used in Example 3 is provided by Oracle and can be used to display customized error messages and codes instead of using the predefined errors.

This procedure expects two parameters: an error number and an error message.

Error number: must be in the range -20000 to -20999

Error message: character string; must be less than 512 characters.

Mutating and Constraining Tables

There are some restrictions on the tables that a statement in the trigger body can access.

A mutating table is a table that is being modified by an insert, update or delete statement. For triggers, it is the table on which the trigger is declared. The SQL statements in a trigger may not read from or modify a mutating table. Note that you can access the "pseudo rows" - the rows prefixed by :old and :new.

The following trigger will cause an error; this trigger is an update trigger on the Manager Number column of the PORTFOLIO table. It attempts to restrict the number of portfolios a manager can have as a result of an update:

create or replace trigger limit_portfolios
after update of managerno on portfolio
for each row
declare
number_pfnos binary_integer;
begin
    select count(*) into number_pfnos
       from portfolio
       where managerno = :new.managerno;
    if (number_pfnos > 10) then
     raise_application_error(-20001, 'Too many portfolios');
    end if;
end;
/

An INSERT that inserts a single row does not treat the triggering table as a mutating table. This type of trigger can read from and modify the triggering table. If the LIMIT_PORTFOLIOS trigger had been established on an INSERT, it would not cause an error.

A constraining table is a table that needs to be read from to enforce a referential integrity constaint. A trigger can not read from or modify the key columns in a constraining table.

If the CASH_NOTICE table in Example 1 had included a foreign key clause referencing PORTFOLIO, the CASH_WARNING trigger would fail since the insert in the trigger body:

insert into cash_notice values (:new.portfoliono, sysdate, :new.managerno, :new.cash);

would modify a constraining table.