Yes, it is feasible. I have seen people complaining about getting
ORA-3113 error in 9.0.1 when creating INSTEAD OF trigger on union all
view. But the error can not be reproduced in 9i release 2. Other than
that, I am not aware of any issues related to this. Of course you need
to be aware of the rules that apply to this particular type of
trigger.
jaywalk_at_techie.com (Jaywalk) wrote in message news:<8f27f83a.0301220551.613c1283_at_posting.google.com>...
> 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 - 18:46:40 CST
