Example 3: Triggers that Logs Changes

One use of triggers is to keep a journal of changes that have been made to the database. In this example, four triggers are shown that log the changes made to acctmgr table in the example bookshop database to another database named changelog which contains the following two table definitions.

create table log_stmt
(
    logtime     TIMESTAMP KEY,
    evtype          CHAR(6),        /* "after" or "before" */
    stmttype        CHAR(8) KEY,    /* "insert", "update", or "delete" */
    tabname     VARCHAR(20),
    comment     CHAR(24)
);
create table log_acctmgr
(
    logtime     TIMESTAMP KEY,
    evtype          CHAR(6),        /* "after" or "before" */
    stmttype        CHAR(8) KEY,    /* "insert", "update", or "delete" */
    comment     CHAR(24),
    mgrid           CHAR(7) KEY,
    name            CHAR(24),
    hire_date       DATE,
    commission  DECIMAL(4,3)
);

The log_stmt table will contain rows that, through the statement-level triggers described below, tracks the execution of each insert, update, and delete statement that occurs on the bookshop database (for this example, only those executed on the acctmgr table). The log_acctmgr table will contain rows that, through the row-level triggers described below, tracks the actual values stored or changed in the acctmgr table.

The before triggers always execute. However, the after triggers only execute when the insert, update, or delete statement which fired the trigger executed successfully. So, the presence in the log tables of a "before" row without an associated "after" row would show that the statement did not have a successful return. We will illustrate this using four triggers on the acctmgr table: two statement-level triggers (before insert and after insert) and two row-level triggers (also before insert and after insert). The create trigger statements are given below.

create trigger bookshop.sib_acctmgr
    before insert on acctmgr
    for each statement 
    insert into changelog.log_stmt values 
       current_timestamp(), "before", "insert", "acctmgr", "sib_acctmgr";
create trigger bookshop.sia_acctmgr
    after insert on acctmgr
    for each statement 
    insert into changelog.log_stmt values 
       current_timestamp(), "after", "insert", "acctmgr", "sia_acctmgr";
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.20 then
        signal sqlcode eCHECK set message_text = "commission must be >= 0.0 and <= 0.20";
    else
insert into changelog.log_acctmgr values 
    current_timestamp(), "before", "insert", "rib_acctmgr", 
            new_acctmgr.mgrid,
            new_acctmgr.name,
            new_acctmgr.hire_date,
            new_acctmgr.commission;
        set new_acctmgr.mgrid = ucase(new_acctmgr.mgrid);
    end if;
end;
create trigger bookshop.ria_acctmgr
    after insert
    on acctmgr
    referencing new row as new_acctmgr
    for each row 
begin atomic
        insert into changelog.log_acctmgr values 
            current_timestamp(), "after", "insert", "ria_acctmgr", 
            new_acctmgr.mgrid,
            new_acctmgr.name,
            new_acctmgr.hire_date,
            new_acctmgr.commission;
end;

Note that rib_acctmgr is the same as shown in the earlier example except for the additional insert into the log_acctmgr table. The rdm-sql shown below shows the effects of these triggers on an insert into the acctmgr table.

rdm-sql: insert into acctmgr values "joe", "Fox, Joe", "1998-12-18", 0.025;
*** 1 row(s) inserted
rdm-sql: select * from acctmgr;
mgrid  | name                    |hire_date |commission
-------+-------------------------+----------+----------
JOE    | Fox, Joe                |1998-12-18|     0.025
rdm-sql: select * from log_stmt;
logtime            |evtype|stmttype|tabname     |comment
-------------------+------+--------+------------+------------
2017-05-12 14:54:51|before|insert  |acctmgr     |sib_acctmgr
2017-05-12 14:54:51|after |insert  |acctmgr     |sia_acctmgr
rdm-sql: select * from log_acctmgr;
logtime            |evtype|stmttype|comment    |mgrid| name    |hire_date |commission
-------------------+------+--------+-----------+-----+---------+----------+----------
2017-05-12 14:54:51|before|insert  |rib_acctmgr|joe  | Fox, Joe|1998-12-18|     0.025
2017-05-12 14:54:51|after |insert  |ria_acctmgr|JOE  | Fox, Joe|1998-12-18|     0.025