Updating table through view in oracle

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.

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.

Maria DB stores an IS_UPDATABLE flag with each view, so it is always possible to see if Maria DB considers a view updatable (although not necessarily insertable) by querying the IS_UPDATABLE column in the INFORMATION_SCHEMA. The WITH CHECK OPTION clause is used to prevent updates or inserts to views unless the WHERE clause in the SELECT statement is true. WITH LOCAL CHECK OPTION restricts the CHECK OPTION to only the view being defined, while WITH CASCADED CHECK OPTION checks all underlying views as well.

CASCADED is treated as default if neither keyword is given.

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table.

The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table.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.It’s good to see the i Series continue to receive the enhancements that other versions of DB2 have. INSTEAD OF triggers make complex views look like a single updateable table to these tools.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 Item View Old For Each Row Mode DB2SQL Begin Atomic Delete From Item Master Where Item=Item View Old. In Microsoft Access, for example, I was able to use the Linked Table feature to link to the DB2 Item View view using ODBC.

Leave a Reply