Example 1: Row-level Before Insert Trigger

A simple before insert trigger can be used to illustrate how triggers can be used to validate new data values and to convert them into a standard format. The acctmgr table in the example bookshop database stores basic information about the bookshop employees who manage the book sales. Each one has an up to 7 character identifier (mgrid) which consists solely of upper case letters. This table also stores each account manager's commission rate–none of which can be larger than 10%. The create table for acctmgr is shown below.

CREATE TABLE acctmgr
(
    mgrid CHAR(7) PRIMARY KEY,
    name CHAR(24),
    hire_date DATE,
    commission DECIMAL(4,3)
);

The trigger definition below, named rib_acctmgr (we're using the "rib" prefix to identify the trigger as a row-level insert before trigger), will fire (execute) whenever a new row is inserted into the acctmgr table.

create trigger bookshop.rib_acctmgr
    before insert on acctmgr
    referencing new row as new_acctmgr
    for each row 
begin atomic
    if new_acctmgr.commission not between 0.0 and 0.10 then
        signal sqlcode eCHECK set message_text = "commission must be >= 0.0 and <= 0.10";
    else
        set new_acctmgr.mgrid = ucase(new_acctmgr.mgrid);
    end if;
end;

The following rdm-sql script shows how the trigger works.

rdm-sql: select * from bookshop.acctmgr;
mgrid  | name                    | hire_date | commission
-------+-------------------------+-----------+-----------
ALFRED | Kralik, Alfred          | 1997-07-02|      0.025
AMY    | Zonn, Amy               | 1994-07-06|      0.025
BARNEY | Noble, Barney           | 1972-05-08|      0.035
FRANK  | Doel, Frank             | 1987-02-13|      0.030
JOE    | Fox, Joe                | 1998-12-18|      0.025
KATE   | Kelly, Kathleen         | 1998-12-18|      0.025
KLARA  | Novac, Klara            | 1990-01-02|      0.025

The current contents of the acctmgr table is shown above. The insert statement below attempts to insert a row with a 25% commission rate. This is trapped by the trigger which returns a check clause violation error.

rdm-sql: insert into acctmgr values "russ", "Wilson, Russell", date "2012-04-27", 0.25;
error: Check clause violation: commission must be >= 0.0 and <= 0.20

The corrected insert is shown below but note that the mgrid value is specified as lower-case. The trigger will correct this so that the upper-case form is inserted into the acctmgr table.

rdm-sql: insert into acctmgr values "russ", "Wilson, Russell", date "2012-04-27", 0.025;
*** 1 row(s) inserted
rdm-sql: select * from acctmgr;
mgrid  | name                    | hire_date | commission
-------+-------------------------+-----------+-----------
ALFRED | Kralik, Alfred          | 1997-07-02|      0.025
AMY    | Zonn, Amy               | 1994-07-06|      0.025
BARNEY | Noble, Barney           | 1972-05-08|      0.035
FRANK  | Doel, Frank             | 1987-02-13|      0.030
JOE    | Fox, Joe                | 1998-12-18|      0.025
KATE   | Kelly, Kathleen         | 1998-12-18|      0.025
KLARA  | Novac, Klara            | 1990-01-02|      0.025
RUSS   | Wilson, Russell         | 2012-04-27|      0.025