Updating table through view in oracle

Effectively, these triggers tell the database manager what code should be run. However, when i Series 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 letting the database manager to do the modification itself, which would be impossible for it to do! INSTEAD OF triggers can save some integration hassle in this situation.

Item; Delete From Item Ext Where Item=Item View Old. Weight); End; Create Trigger DATALIB/ITEMUPDATE Instead Of Update On DATALIB/ITEMVIEW Referencing Old As Item View Old New As Item View New For Each Row Mode DB2SQL Begin Atomic Update Item Master Set Item=Item View New. This handy feature allows an i Series (or other remote database) table or view to be updated by Microsoft’s Access database.

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. The Access form shown in Figure 2 was built in just a few minutes.

For simplicity, consider the following common scenario: An i Series 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.

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 Item View New For Each Row Mode DB2SQL Begin Atomic Insert Into Item Master Values(Item View New. Showing the description from the Item Master table improves the form’s usability.

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.

To summarize, simplicity of coding and overcoming limitations imposed by various products are the key advantage I see for INSTEAD OF triggers. Weight); End If; Thankfully, IBM continues to enhance DB2/400 on the i Series to have features similar to those available in SQL Server, Oracle 10g, and other versions of DB2. Codd’s abstraction of a relational database as one large table just took one step closer to reality on the i Series.

Of course, INSTEAD OF triggers are not replacements for stored procedures! INSTEAD OF triggers effectively allow programmers to treat a multiple table view as a single table capable of inserts, updates and deletes. Stay tuned, since INSTEAD OF triggers are only one of many splendid DB2 enhancements released in i5/OS V5R4.

Leave a Reply