Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers

Published: March 8, 2006

by Michael Sansoterra

Sometimes using SQL to perform simultaneous inserts, updates and deletes against related tables can be a chore. It would be convenient if programmers could simply treat related tables as a single table for the purpose of modifying data.

For simplicity, consider the following common scenario: An iSeries has multiple item tables holding data related in a one-to-one relationship. Often, this scenario is due to multiple application packages having item master files that are both populated with common item data. The illustration in Figure 1 below shows an item master table from Application A along with a supplemental custom item extension table; this table, as the name suggsts, provides supplemental columns not provided by the application's Item Master.

A typical view to join the two tables might look like the following:

Create View DATALIB/ItemView
As
   Select IM.Item, IM.ItemDesc, IM.ItemType,
          IE.Height, IE.Length, IE.Width, 
          IE.DimUOM, IE.Weight
     From DATALIB/ItemMaster IM
     Join DATALIB/ItemExt IE On IE.Item=IM.Item

While it is relatively easy to retrieve and modify the data from both tables using RPG and subfiles, it is not so easy to do with SQL, especially in a client/server application. Usually, to do this would require multiple insert, update, and delete statements for each table in the join. It would be perfect if we could simply issue INSERT, UPDATE, and DELETE statements against the multi-table view ITEMVIEW.

With the DB2/400 database embedded in i5/OS V5R4, these modification statements against multi-table views can be accomplished with a special type of trigger called an INSTEAD OF trigger. INSTEAD OF triggers are special SQL triggers that give the database specific code to run when performing data manipulation (INSERT, UPDATE, and DELETE) against a view. Effectively, these triggers tell the database manager what code should be run. (Instead of letting the database manager to do the modification itself, which would be impossible for it to do!)

On the iSeries, limited support for INSTEAD OF triggers was delivered by PTF SI16101 for i5/OS V5R3 systems. Unfortunately, data manipulation against multi-table views involving Joins or Unions was not yet supported, limiting their usefulness to single table views. This limitation has been lifted in V5R4.

The examples shown in this story were originally developed on DB2 UDB for Windows V8.2.1 and required very little modification to run on the iSeries. It's good to see the iSeries continue to receive the enhancements that other versions of DB2 have.

Consider the following simple example of an INSTEAD OF DELETE trigger that is created on a view called ITEMVIEW:

Create Trigger DATALIB/ITEMDELETE 
Instead Of Delete On DATALIB/ITEMVIEW  
Referencing Old As ItemViewOld  
For Each Row Mode DB2SQL
Begin Atomic
    Delete From ItemMaster 
     Where Item=ItemViewOld.Item;
    Delete From ItemExt 
     Where Item=ItemViewOld.Item;
End;

When a DELETE statement is issued against view ITEMVIEW, the code in the INSTEAD OF trigger will run and delete the item row in both tables. Whereas a DELETE would normally fail against such a view, it now succeeds because of the alternative code provided to DB2. Of course this is just one possibility, as we could have just as easy coded the “delete” trigger to actually update a deleted flag column to ‘Y’ in the base tables.

By creating INSTEAD OF triggers on update and insert events, view ITEMVIEW will become fully modifiable. All we need to supply in our trigger code is the logic appropriate to modify the tables as shown in the following two triggers:

Create Trigger DATALIB/ITEMINSERT 
Instead Of Insert On DATALIB/ITEMVIEW  
Referencing New AS ItemViewNew 
For Each Row Mode DB2SQL 
Begin Atomic
    Insert Into ItemMaster
    Values(ItemViewNew.Item,ItemViewNew.ItemDesc,
           ItemViewNew.ItemType);

    Insert Into ItemExt
    Values(ItemViewNew.Item,ItemViewNew.Height,
           ItemViewNew.Length,ItemViewNew.Width,
           ItemViewNew.DimUom,ItemViewNew.Weight);
End;

Create Trigger DATALIB/ITEMUPDATE 
Instead Of Update On DATALIB/ITEMVIEW  
Referencing Old As ItemViewOld  
            New As ItemViewNew  
For Each Row Mode DB2SQL 
Begin Atomic
    Update ItemMaster 
       Set Item=ItemViewNew.Item,
             ItemDesc=ItemViewNew.ItemDesc,
             ItemType=ItemViewNew.ItemType
     Where Item=ItemViewOld.Item;

    Update ItemExt
       Set Item=ItemViewNew.Item,
           Height=ItemViewNew.Height,
           Length=ItemViewNew.Length,
           Width=ItemViewNew.Width,
           DimUOM=ItemViewNew.DimUom,
           Weight=ItemViewNew.Weight
     Where Item=ItemViewOld.Item;
End;

The name specified in the old, new portions of the Referencing clause are used to refer to columns in the row being modified--in the case of the INSTEAD OF UPDATE trigger, ItemViewOld and ItemViewNew represent the before and after pictures respectively. These are called transition variables.

Why would we want to use triggers on a view instead of, say a stored procedure, which could accomplish the same thing? The biggest benefit I see for this feature is in the client/server realm. For instance, many third-party, PC-based packages offer simple ODBC interfaces that can only link to a single table to do data updates. However, when iSeries customers hook these packages to their DB2-based ERP packages, this limitation is a hassle when the data is stored in multiple tables. INSTEAD OF triggers can save some integration hassle in this situation.

Many PC developer tools--for example, those that come with Visual Studio .NET--can be used to build code for creating updateable Windows forms or Web pages based on a single table but cannot do so against multiple table views (without lots of extra code). INSTEAD OF triggers make complex views look like a single updateable table to these tools.

In Microsoft Access, for example, I was able to use the Linked Table feature to link to the DB2 ItemView view using ODBC. This handy feature allows an iSeries (or other remote database) table or view to be updated by Microsoft's Access database. The Access form shown in Figure 2 was built in just a few minutes. The application form is fully functional, with the ability to modify data in both underlying tables, although Access is oblivious to the fact that there are actually two tables being changed.

The beauty here is that both related tables are present in a single form and can be updated simultaneously. The form would not be nearly as effective if it showed, for example, only the item extension data. Showing the description from the Item Master table improves the form's usability. Allowing the Item Master fields to be updated, if desired, is an added bonus.

Yet another benefit to using INSTEAD OF triggers is that database programmers can maintain complex update code within their own realm while supplying a single table, which is easier than stored procedures, for client side programmers to use. To summarize, simplicity of coding and overcoming limitations imposed by various products are the key advantage I see for INSTEAD OF triggers. Of course, INSTEAD OF triggers are not replacements for stored procedures!

Allowing updates against joined tables is only one example of the formerly impossible. INSTEAD OF triggers can also be defined on views that combine tables "vertically" using UNION or UNION ALL, allowing them to be updateable as well.

Here are a few more thoughts on programming:

• It's up to the programmer to supply meaningful code in the body of each trigger type. It is possible to make each type of INSTEAD OF trigger do something other than what it is supposed to do.
• INSTEAD OF triggers may only be created on views. They cannot be created on tables. Unfortunately, in my testing they would not work on JOIN logicals, either.
• Obviously, VIEWs are SQL-based, but I was able to update a VIEW defined with an INSTEAD OF trigger using native I/O in an RPG program. However, I could not update it using the UPDDTA command.
• For rare but useful coding, User-Defined Table functions can theoretically be extended by using a view (if the table function's parameters can be resolved within the view) and then endowed with INSTEAD OF trigger code to perform various data modification logic.
• To be effective with many PC tools, including Access and Visual Studio .NET, the view should have an identifiable unique key combination. This is easy enough to do in a one-to-one join as shown here, but can sometimes be difficult to identify in a one-to-many relationship for those many legacy applications do not define unique key values on their detail tables.
• The DB2 Signal statement can be used to create an error condition, causing the data modification statement to fail.
• Views made updateable by INSTEAD OF triggers are not supported in updateable cursors.
• Columns containing expressions can be included in these views as well. The trigger code can either use the values supplied during inserts and updates or simply ignore these columns.
• Only one trigger is allowed on a view for an INSERT, UPDATE, or DELETE, thereby limiting the number of triggers on a view to three.

You can build as much logic as necessary into your views. For example, if our ITEMVIEW view was re-created with a LEFT OUTER JOIN, we could easily enhance the trigger code to use something like this to optionally insert a row into the item extension table only if one or more column values were present:

If ItemViewNew.Height Is Not Null
… etc
Or ItemViewNew.Weight Is Not Null Then
    Insert Into ItemExt
    Values(ItemViewNew.Item,ItemViewNew.Height,
ItemViewNew.Length,ItemViewNew.Width,
ItemViewNew.DimUom,ItemViewNew.Weight);
End If;

Thankfully, IBM continues to enhance DB2/400 on the iSeries to have features similar to those available in SQL Server, Oracle 10g, and other versions of DB2. INSTEAD OF triggers effectively allow programmers to treat a multiple table view as a single table capable of inserts, updates and deletes. E.F. Codd's abstraction of a relational database as one large table just took one step closer to reality on the iSeries.

Stay tuned, since INSTEAD OF triggers are only one of many splendid DB2 enhancements released in i5/OS V5R4.