Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers
March 8, 2006 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:
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.