Example 2: Row-level Before Update Trigger
The trigger in the last example only fires when a new row is inserted into the acctmgr table. But the mgrid and commission values of an existing acctmgr row could be changed using an update statement. The before update trigger on the acctmgr table given below (rub_acctmgr) can be created to perform the same checks that the rib_acctmgr trigger performed, but only when either the mgrid and/or the commission columns are changed.
create trigger bookshop.rub_acctmgr
before update of mgrid, commission 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 rdm-sql script below shows the effects of the execution of this trigger from the two highlighted update statements.
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 *** 7 row(s) returned rdm-sql: update acctmgr set commission = 0.25 where mgrid = 'JOE'; error: Check clause violation: commission must be >= 0.0 and <= 0.10 rdm-sql: update acctmgr set mgrid = 'al' where mgrid = 'ALFRED'; *** 1 row(s) updated rdm-sql: select * from acctmgr; mgrid | name | hire_date | commission -------+-------------------------+-----------+----------- AL | 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 *** 7 row(s) returned