During a session with a potential customer/partner I was posed the following question re. Virtuoso's Virtual Database functionality:

"Can I create an updateable SQL VIEW in Virtuoso that would comprise columns from 3rd party databases such as Oracle, SQL Server, and say MySQL".

The answer was yes,based onthe fact that Virtuoso does support SQL INSTEAD-OF Triggers - even in Virtual Database mode.

I am certainly keen to see if any other Virtual Database style products achieve this feat (which is trying for many homogeneous SQL database engines).

Dr. Paul Dorsey of Dulcian, Inc. wrote a very good article about this subject, and here is an excerpt from his article overiew:

Views are an important part of application development. Since Oracle 7.3, we quickly recognized the importance of using Oracle’s updateable view feature. An updateable view allows you to join several tables and perform updates against the driving table. For example, if you join EMP and DEPT in the traditional way and display columns from both tables, DML operations are possible against EMP but not DEPT.

For traditional relational database designs, this is enough functionality. For example, in a typical Forms application, when you are basing a block on a table, the additional columns that you want to display are lookups from other tables and can therefore be easily supported using traditional updateable views. These views are built using a combination of joins and outer joins or, in extreme cases, looking up additional information through functions embedded in the views. Under no circumstances should post query triggers be used to support this functionality. Post query triggers cause unnecessary network traffic and also embed the logic in the application rather than in the database or somewhere else where it can easily be reused.

What happens in a situation where the information you want to display in the block requires a query that is so complex that your ability to maintain (insert, update, delete) that information using a simple updateable view is eliminated? The updateable views are relatively restrictive. Only a single table can be updated. Joins must be created carefully and based on Foreign Key constraints in the database. No set operators such as UNION or MINUS can be used. For these reasons, it is common to end up with a block that cannot be updated as required. How do most developers handle this situation?

<!--[if !supportLists]-->a) <!--[endif]-->By placing complex logic in the form (WHEN-VALIDATE-ITEM triggers)

<!--[if !supportLists]-->b) <!--[endif]-->By writing procedures that access Forms’ ability to replace the Insert, Update, Delete routines and place that logic in the form

These practices are just as undesirable as using POST-QUERY triggers. The logic is in the wrong place and is not reusable.

The INSTEAD-OF trigger views feature was introduced by Oracle in version 8.15. This feature enables developers to create views on single or multiple tables or any other view imaginable by writing INSTEAD-OF triggers that tell the view how to behave when Inserts, Updates or Deletes are issued. Peter Koletzke and I first wrote about this feature in our Oracle Press book Oracle Developer: Advanced Forms & Reports (2000). At the time, we gave the feature relatively brief mention because we believed that most of the systems we were building included blocks based on traditional updateable views, which allow updates to a single table. Now, there is a good reason to look more closely at INSTEAD-OF trigger views.

Database Journal also has an article on this subject.