Introduction

INSTEAD OF triggers provide a method of modifying views that are not updateable using ordinary insert, update and delete statements. INSTEAD OF triggers can also be used to make a stepwise migration of a data model.

Description

The definition of a trigger includes an event time that can be:

  • before
  • after
  • instead of

If a trigger has the event time instead of, the statement that caused the trigger will be replaced by a call to the trigger code. In an application it will appear as an ordinary data change statement but the trigger may convert data or split data into different tables.
INSTEAD OF triggers can only be defined for views.

Techniques

As an example, consider a database in which date and time values are stored in character format (YYYYMMDD and HHMMSS). If we wish to change these columns using the date and time data types instead, we can do it without having to change all applications using this table. To accomplish this, we take the following steps:

  1. Create a new table with the date and time column:
     Create table wake_up (roomno char(7),wake_date date,
                           wake_time time, primary key(roomno,wake_date))
  2. Create functions for converting values in the old format:
     create function convertCharToDate(oldDate char(8))
     returns date
     return cast(substring(oldDate from 1 for 4) || '-' ||
                 substring(oldDate from 5 for 2) || '-' ||
                 substring(oldDate from 7 for 2) as date)
    
     create function convertCharToTime(oldTime char(6))
     returns time
     return cast(substring(oldTime from 1 for 2) || ':' ||
                 substring(oldTime from 3 for 2) || ':' ||
                 substring(oldTime from 5 for 2) as time)
    
     create function convertDateToChar(newDate Date)
     returns char(8)
     begin
        declare cdate char(10);
        set cdate = substring(cast(newDate as char(20)) from 6 for 10);
        return substring(cdate from 1 for 4) ||
               substring(cdate from 6 for 2) ||
               substring(cdate from 9 for 2);
     end
    
     create function convertTimeToChar(newTime Time)
     returns char(6)
     begin
        declare ctime char(8);
        set ctime = substring(cast(newTime as char(20)) from 6 for 8);
        return substring(ctime from 1 for 2) ||
               substring(ctime from 4 for 2) ||
               substring(ctime from 7 for 2);
     end
  3. Migrate the data to the new table:
     Insert into wake_up select roomno,
                                convertCharToDate(wake_date),
                                convertCharToTime(wake_time) from wakeup
  4. Drop the old table:
     Drop table wakeup cascade
  5. Create a view with the same name and columns (name and type) as the old table:
     Create view wakeup as select roomno,
                                  ConvertDateToChar(wake_date) as wake_date,
                                  ConvertTimeToChar(wake_time) as wake_time from wake_up
  6. Create INSTEAD OF triggers for insert, update and delete operation that handles the conversion from character format to date:
     Create trigger dateConvertInsert instead of insert on wakeup
     referencing new table as newRows
     insert into wake_up select roomno, convertCharToDate(wake_date),
                                convertCharToTime(wake_time)
                           from newRows
    
     Create trigger dateConvertUpdate instead of update on wakeup
     referencing old table as oldRows new table as newRows
     begin atomic
        declare oldRows cursor for select roomno, wake_date from oldRows;
        declare newRows cursor for select wake_time from newRows;
        declare oldRoomno char(7);
        declare oldWake_date char(8);
        declare newWake_time char(6);
        open oldRows;
        open newRows;
        begin
           declare exit handler for not found
           begin end;
           loop
              fetch oldRows into oldRoomno, oldWake_date;
              fetch newRows into newWake_time;
              update wake_up set wake_time = convertCharToTime(newWake_time)
                 where roomno = oldRoomno 
                   and wake_date = convertCharToDate(oldWake_Date);
           end loop;
        end;
        close oldRows;
        close newRows;
     end

A delete trigger is very simple to write and we encourage you to write one as an exercise.
The application that uses the table employees will still function without needing to be rewritten. Although this example is simplified and contrived it shows the possibilities of INSTEAD OF triggers. Another functional deployment of INSTEAD OF triggers is to make joinviews updateable.

Benefits

With INSTEAD OF triggers you can make any view updateable, thus allowing a stepwise migration of a data model. You can also carry out complex validations of data that are inserted through a view.

Links

Mimer SQL Documentation Set, PDF-format or HTML navigation, found at our Documentation page »
See the Reference Manual part of the documentation set.

Graphic Element - Cube