Re: Using INSTEAD OF trigger to update a UNION ALL view.

Jaywalk wrote:

> 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.

Do it and benchmark it.

Daniel Morgan Received on Mon Jan 20 2003 - 10:41:39 CST