--- In IB-Architect@y..., "Claudio Valderrama C." <cvalde@u...> wrote:

> Hello.
>
> Updatable views are nice, eh? After a week <of pain ...>

>
> The power of a view (for me) comes when you define triggers that

-unlike

> triggers on a table- are able to change the semantics of operations:

<...>

>
> Until now, I've shown the bright side. Now it comes the problem:

some of my

> views operate on a single table. They don't need triggers on them to

be

> updatable. But since I write triggers for them to alter semantics or

change

> some fields before they are stored, both the trigger and the direct

action

> are executed. This is a headache. Now if you insert in such view,

the direct

> INSERT command applies to the underlying table (automagically)

followed by

> the INSERT carried by the defined trigger. <...>

> I again found myself this time doing in these simple views my old

trick

> create view v as select ...
> from tbl join rdb$database on 1=1;
>
> just to trick the engine and force the view to be only updatable by

my

> triggers, not directly. There should be a better solution. Either a

reserved

> word to make a view explicitly non-automagically updatable or a

mechanism in

> place so when triggers are defined on a view that's already

updatable, the

> "natural" behavior won't fire and only the user triggers will

execute.

>
> The topic was proposed at Mers a year ago but I don't remember any

solution.

> Ideas? I'm not looking for workarounds, but for ideas to enhance the

engine.

I think Oracle (in 8i?) approached this by adding support for "INSTEAD
OF" triggers -- these is a vendor-specific extension (i.e. not part of
SQL99) that allows you to say that your trigger logic should be
executed instead of whatever the database would have attempted to do
for you.

I don't have reference info nearby, but I believe the syntax was
something like:
CREATE TRIGGER blat
INSTEAD OF {INSERT|UPDATE|DELETE} ON blatview
<...>

[Note: I'm not suggesting that this is a good or bad approach, just
passing along the info, for what it's worth.
I can't recall if this syntax has been proposed to the SQL standard
committee or not ...]

I think they also added support for (vendor-specific) keywords WITH
READ ONLY if you want to make a view read-only. Whatever ...

db