Example 5: Delete Triggers to Manage Loss of an Account Manager

When the book shop loses an account manager (they quit, are let go, or–heaven forbid–die), something needs to be done with the customers they serviced. In the bookshop database the acctmgr table is referenced by restricted foreign keys declared in the patron table and the auction table. A delete of an acctmgr table row which has active referencing rows will fail with a referential integrity error. Our book shop has a policy where when an account manager becomes no longer employed, his or her customers are reassigned to Barney (the shop owner) who will service them himself until he later reassigns each of those customers to another account manager.

In addition, a record of all past account managers who have worked at the shop needs to be maintained. To facilitate this, the bookstats database which was introduced above in Example 4: Triggers that Track Sales Statistics to track monthly sales for each account manager, has a table named past_acctmgr defined as shown below.

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

When an acctmgr row is deleted, its row values along with the deletion date are inserted into the past_acctmgr table. This also serves to preserve the record of that account manager's past sales by keeping the mgrid identification.

A row-level before delete trigger on the acctmgr table will be used to reassign the deleted acctmgr row's patrons and auctions to Barney so that the when the delete is executed it will not fail with a referential integrity error. A row-level after delete trigger on the acctmgr table will be used to insert into the past_acctmgr table a row containing the deleted acctmgr's row values while setting the exit_date column to the current date. These two trigger declarations are as follows.

create trigger bookshop.rdb_acctmgr
before delete on acctmgr
referencing old row as old_acctmgr
for each row 
begin atomic
/* when an acctmgr leaves, his/her data is reassigned to BARNEY 
   who will re-assign their data to another acctmgr later.
*/
declare continue handler for not found
begin -- in case there are no rows in patron, auction for mgrid
end;
update patron set mgrid = "BARNEY" where mgrid = old_acctmgr.mgrid;
update auction set mgrid = "BARNEY" where mgrid = old_acctmgr.mgrid;
end;
create trigger bookshop.rda_acctmgr
after delete on acctmgr
referencing old row as old_acctmgr
for each row 
insert into bookstats.past_acctmgr values 
old_acctmgr.mgrid, old_acctmgr.name, old_acctmgr.hire_date, 
current_date(), old_acctmgr.commission;

Note that the rdb_acctmgr trigger contains an empty continue handler for status not found. This is necessary in case one, or both, of the update statements raise the not found status. Otherwise, the trigger would return the not found status and the delete statement would not be executed. The rdm-sql script shown below demonstrates the effects of these triggers.

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
rdm-sql: select mgrid, name from patron where mgrid in ('BARNEY','KATE');
mgrid  | name
-------+-------------------------------
BARNEY | Carlos Slim Helu
KATE   | Stephen Jobs
KATE   | Scrooge McDuck
BARNEY | Bruce Wayne
KATE   | Jay Gatsby

The above two select statements show the current acctmgr rows and those patrons assigned to Barney and Kate. The delete below will fire the two triggers with the first reassigning Kate's patrons to Barney and the second adding Kate to the past_acctmgrs table (in database bookstats). The three select statements that follow show the results.

rdm-sql: delete from acctmgr where mgrid='KATE';
*** 1 row(s) deleted
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
KLARA  | Novac, Klara            | 1990-01-02|      0.025
rdm-sql: select mgrid, name from patron where mgrid in ('BARNEY','KATE');
mgrid  | name
-------+-------------------------------
BARNEY | Carlos Slim Helu
BARNEY | Stephen Jobs
BARNEY | Scrooge McDuck
BARNEY | Bruce Wayne
BARNEY | Jay Gatsby
rdm-sql: select * from past_acctmgr;
mgrid  | name                    | hire_date | exit_date | commission
-------+-------------------------+-----------+-----------+-----------
KATE   | Kelly, Kathleen         | 1998-12-18| 2017-06-05|      0.025

There is one more potential problem that needs to be addressed. As the situation now exists, it is possible that a new account manager can be added who could have the same mgrid value as a past manager. To prevent this from happening, the earlier row-level before insert trigger on the acctmgr table, rib_acctmgr, is changed to be as shown below.

create trigger bookshop.rib_acctmgr
before insert on acctmgr
referencing new row as new_acctmgr
for each row 
begin atomic
    declare has_pastmgr boolean default true;
    declare new_mgrid, old_mgrid char(7);
    declare get_pastmgr cursor for 
        select mgrid from bookstats.past_acctmgr where mgrid = new_mgrid;
    declare continue handler for not found
    begin
        set has_pastmgr = false;
    end;
    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
        set new_mgrid = ucase(new_acctmgr.mgrid);
        open get_pastmgr;
        fetch get_pastmgr into old_mgrid;
        close get_pastmgr;
        if has_pastmgr = true then
            signal sqlcode eDUPLICATE 
                set message_text = "past acctmgr has same mgrid";
        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 = new_mgrid;
        end if;
    end if;
end;

In the previous example, Kathleen Kelly was deleted from the acctmgr table. The insert below attempts to insert a new acctmgr whose mgrid is 'KATE'. The above trigger detects this and rejects the insert.

rdm-sql: insert into acctmgr values 'kate', "Middleton, Kate", current_date(), 0.025;
error: Attempt to insert a duplicate value as a unique/primary key: 
    past acctmgr has same mgrid
rdm-sql: insert into acctmgr values 'prncess', "Middleton, Kate", current_date(), 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
KLARA  | Novac, Klara            | 1990-01-02|      0.025
PRNCESS| Middleton, Kate         | 2017-06-05|      0.025