Example 4: Triggers that Track Sales Statistics

Another common use of triggers is to maintain a set of usage statistics. The trigger in this example updates a row of the following table stored in a database named bookstats whenever a new sale table row is inserted in the bookshop database.

create table bookstats.acctmgr_stats
(
    mgrid       CHAR(7),
    sales_moyr  INTEGER,
    sales_num   SMALLINT,
    sales_tot   DECIMAL(12,2),
    PRIMARY KEY(mgrid, sales_moyr)
);

Each row of this table contains the monthly sales total for the bookshop account manager with the specified mgrid. The after insert row-level trigger below will update (or create) the appropriate row in the acctmgr_stats table based on the values specified in the insert statement as well as set the date_sold column in the book table.

create trigger bookshop.ria_sale
    after insert on sale
    referencing new row as n_sale
    for each row 
begin atomic
    declare sell_date date;             -- set to the current date
    declare yrmo_sold integer;          -- computed from sell_date
    declare mgr_id char(7);             -- fetched from patron row
    declare sell_price decimal(10,2);   -- fetched from book row
    /* cursor to fetch the mgrid from the associated patron */
    declare get_patron cursor for select mgrid from patron where patid = n_sale.patid;
    /* cursor to fetch the price of the associated book */
    declare get_book cursor for select price from book where bookid = n_sale.bookid;
    /* Only the update to acctmgr_stats below can return not found,
        so create a new stats row for this mgrid, year/month.           
    */
    declare continue handler for not found
    begin
        insert into bookstats.acctmgr_stats values mgr_id, yrmo_sold, 1, sell_price;
    end;
    /* set current date and compute year/month value */
    set sell_date = current_date();
    set yrmo_sold = year(sell_date)*100 + month(sell_date);
    /* Fetch mgrid from patron row */
    open get_patron;
    fetch get_patron into mgr_id;
    close get_patron;
    /* fetch price of book from book row */
    open get_book;
    fetch get_book into sell_price;
    close get_book;
    /* update the stats for this acctmgr and year/month */
    update bookstats.acctmgr_stats 
        set sales_num = sales_num + 1, sales_tot = sales_tot + sell_price
        where mgrid = mgr_id and sales_moyr = yrmo_sold;
    /* need to indicate that/when the book has been sold */
    update book set date_sold = sell_date where bookid = n_sale.bookid;
end;

The query below shows the contents of the acctmgr_stats table after importing the sales data from the sales.txt file. Note that the sales_moyr column values will be for the year and month in which you run this test.

rdm-sql: select * from acctmgr_stats;
mgrid  |  sales_moyr| sales_num|      sales_tot
-------+------------+----------+---------------
ALFRED |      201705|        19|       92625.00
AMY    |      201705|         9|       55975.00
BARNEY |      201705|         7|       54460.00
FRANK  |      201705|         5|       41745.00
JOE    |      201705|        19|      103377.00
KATE   |      201705|        14|       59875.00
KLARA  |      201705|        22|       92280.00