Using INSTEAD OF trigger to update a UNION ALL view.

I'm working on a problem where a colleague wants to merge together three unrelated tables with a similar key (it was originally a single mainframe file) because we might someday want to do updates on the merged result. I want to propose using a UNION ALL view and an INSTEAD OF trigger to avoid denormalizing the tables. I'd put a hardcoded marker in the view to identify the table and then use a common key to update it. The view would look something like:

     CREATE VIEW all_in_one AS 
        SELECT "t1" marker, keyfield, datafield FROM table1
           UNION ALL
        SELECT "t2" marker, keyfield, datafield FROM table2
           UNION ALL
        SELECT "t3" marker, keyfield, datafield FROM table3 

I probably have the syntax wrong, but you get the idea. The pseudocode for the INSTEAD OF trigger would be something like:

     IF (marker = "t1") THEN
        UPDATE table1 SET datafield = datafield
         WHERE keyfield = keyfield
     IF (marker = "t2") THEN
        UPDATE table2 SET datafield = datafield
         WHERE keyfield = keyfield
     IF (marker = "t3") THEN
        UPDATE table3 SET datafield = datafield
         WHERE keyfield = keyfield
     ELSE 
        Error.
     END IF

Is this idea feasible? Are there any "gotchas" I should be aware of?

Any help would be appreciated.
Jaywalk

P.S. I may have accidently posted only part of this message earlier. Sorryaboutthat. Received on Wed Jan 22 2003 - 07:51:13 CST