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.
The definition of a trigger includes an event time that can be:
- 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.
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:
- 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))
- 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
- Migrate the data to the new table:
Insert into wake_up select roomno, convertCharToDate(wake_date), convertCharToTime(wake_time) from wakeup
- Drop the old table:
Drop table wakeup cascade
- 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
- 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.
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.