Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Trigger Time


The trigger time specifies when, in relation to the execution of the triggering data manipulation statement, the trigger is executed.

The possible values for the trigger time for a base table are:

It is possible to have both row and statement triggers for the same event on a base table. The logic for invoking statement and row triggers for a base table can schematically be seen as:

 
 --
 -- invoke before statement triggers
 --
         call before_statement_trigger_1;
         ...
         call before_statement_trigger_n;
 
         get_data:
         loop
 --
 -- get rows affected by data manipulation statement
 --
             if  not found then
                 leave get_data;
             end if;
 --
 -- invoke before row triggers
 --
             call before_row_trigger_1;
             ...
             call before_row_trigger_n;
 --
 -- save data to old/new table if used
 --
 
 --
 -- do actual operation
 --
             delete/insert/update;
 --
 -- invoke after row triggers (currently not supported)
 --
             call after_row_trigger_1;
             ...
             call after_row_trigger_n;
         end loop;
 --
 -- invoke after statement triggers
 --
     call after_statement_trigger_1;
     ...
     call after_statement_trigger_n;
 

Note that this schema includes after row triggers even though these are not supported in this version of Mimer SQL.

Analogously with base tables, if you have both statement and row trigger the schematical code for invoking triggers would look like

 
     get_data:
     loop
 --
 -- get rows affected by data manipulation statement
 --
         if  not found then
             leave get_data;
         end if;
 --
 -- execute instead of row trigger
 --
         call instead_of_row_trigger_1;
         ...
         call instead_of_row_trigger_n;
 --
 -- save data to old/new table if used
 --
     end loop;
 --
 -- call instead of statement triggers
 --
     call instead_of_statement_trigger_1;
     ...
     call instead_of_statement_trigger_n;
 

Note that this schema includes instead of row triggers even though these are not supported in this version.

Example

Example of an instead of trigger, which can be used for handling join views.

 CREATE TRIGGER book_details_instead_of_update
    INSTEAD OF UPDATE ON mimer_store_book.book_details
 REFERENCING  NEW TABLE AS new_bd
 BEGIN ATOMIC
 --
 -- Update one table with some of the data from the join view
 --
    UPDATE titles
       SET authors_list = (SELECT authors_list
                             FROM new_bd
                            WHERE item_id = titles.item_id)
     WHERE item_id IN (SELECT item_id
                         FROM new_bd);
 --
 -- Update another table using another column from the join view
 --
   UPDATE producers
      SET producer_name = (SELECT publisher
                             FROM new_bd
                            WHERE item_id = producers.producer_id)
    WHERE producer_id IN (SELECT item_id
                            FROM new_bd);
 END
 

Example

The following example describes how triggers can be used to log all changes made to a table:

 create table maintab (c1 integer primary key, c2 varchar(10));
 
 create table logtab (ts timestamp default localtimestamp,
                      username nvarchar(128) collate SQL_IDENTIFIER
                          default session_user,
                      operation varchar(6), 
                      c1old integer, c2old varchar(10),
                      c1new integer, c2new varchar(10));
 
 @
 create trigger maintabinserts after insert on maintab
 referencing new table as newt
 for each statement
 begin atomic
   insert into logtab (operation, c1new, c2new)
     select 'INSERT', newt.c1, newt.c2
     from newt;
 end
 @
 
 @
 create trigger maintabupdates after update on maintab
 referencing new table as newt
             old table as oldt
 for each statement
 begin atomic
   insert into logtab (operation, c1old, c2old, c1new, c2new)
     select 'UPDATE', oldt.c1, oldt.c2, newt.c1, newt.c2
     from oldt, newt
     where oldt.mimer_rowid = newt.mimer_rowid;
 end
 @
 
 @
 create trigger maintabdeletes after delete on maintab
 referencing old table as oldt
 for each statement
 begin atomic
   insert into logtab (operation, c1old, c2old)
     select 'DELETE', oldt.c1, oldt.c2
     from oldt;
 end
 @
 

A trigger's old and new tables' rows are sorted in the same order. This means that if old table data and new table data are fetched in parallel, the corresponding rows will be read even if the primary key has been updated.

This example's update trigger uses the mimer_rowid pseudo-key to ensure the performance when joining the old and new tables.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX