INSTEAD OF Triggers

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

See the Reference Manual in the Mimer SQL Documentation set.