Subject Re: [IB-Architect] Updatable views
Author Ivan Prenosil
First, I must say that I think I do understand
the idea of overriding original view semantics by trigger;
that it can give "better" control of what such view+trigger combo
can do, that it may seem at first sight more clear and consistent, etc...
But I think that such behaviour is more _dangerous_ than useful.

I prefer software that is doing what it is asked to do,
rather than such that tries to do me a favor by "anticipating"
my needs and trying to "help" me by doing some extra work automatically.

When I say "CREATE TRIGGER, please", all I want is to create a trigger.
I do not see any advantage if the server answers "o.k. here is your trigger,
but I think that you will not need the work done previously by that view anymore,
so I am disabling it".


I have database where ordinary users/applications do not have rights
to directly access tables, but can access it only via views and SPs
(security and such things...).
Now I want to add a trigger that fills username and timestamp for each
operation, but I want to add it to (updatable) view, not table (so that
more privileged users, or replication engine, etc. can by-pass it).
- if I simply add new trigger, it will broke the application because
the view will stop doing things it did previously.
- because I know there is this new view-automatially-disabled-by-trigger functionality,
I just need to check few things:
- are there any triggers on this view yet ?
If yes, it is probably safe to add another trigger.
If not, do not forget to also add code to supply functionality
that was previously done by view itself (e.g. insert into original table).
- I can add such code to my new trigger, but I risk that later somebody
adds new "enhanced" trigger (that does not do insert into original
table, because author "knows" that it is already done by another trigger)
and deletes (or disables) my trigger - again application stops working.
- Or I can create one extra trigger that only does original
job of the view. But then I have to ensure that all other newly
created triggers will have correct position to fire before this one.
- I also must remember that the new auxiliary trigger must insert
into physical table, not view, to avoid endless loop.
- and what about RowsAffected? Currently, inserting one row into table with
triggers will return RowsAffected=1, regardless of work done by triggers
on that table. Is this bad ? Why should views behave differently ?

I am sure I am missing something. Can anybody enlighten me?
I can remember only two kinds of complaints people have about views:
1) BUGS. It _is_ serious, but is it good reason for changing basic behaviour ?
2) Sometimes people want to have updatable view with triggers
that behaves like non-updatable view with triggers.
(And current "workaround" is to add join to such view.)

(In SQL92, existence of non-updatable _column_ causes that _whole
view_ becomes non-updatable, e.g. CREATE VIEW V AS SELECT 1,ID FROM TAB.
But I am not proposing to implement such restriction into FB.)

I am prepared to accept _good_ arguments that explain where I am wrong.
No stones, please :-)