Firebird 2 changes the semantics of inserts, updates, and
deletes from naturally updatable views with triggers.

In the early dark ages adding a user trigger to a naturally
updatable view blocked the natural update. During the great
SQL shift in V4, the rules were changed so the natural update
did take place whether or not triggers were present.

Reasonable people may disagree about whether that was a good
thing. However, that is the way that InterBase and Firebird
have worked for nearly 10 years. An update to a simple view
with a trigger changes the database just as if it were an
update to a base table with a trigger. If you want to do
something clever, you have to make the view non-updateable -
joining the single table with RDB$DATABASE for example.

There was discussion and agreement to change the behavior
of naturally updateable views with triggers on the development
list around 30 July 2003 and again in November 2003. Whether
I was on vacation, asleep, or just stupid, I missed it.

Dmitry and others found dozens of cases where views were
marginally updateable, unreliably updateable, crashed the
system etc. Dmitry's message is below.

This is going to break some well-behaving programs. Consider
a simple view like this:

create view v as select * from T;

create trigger v_bi1 before insert
as insert into LOG (who, what, when)
values (CURRENT_USER, 'View v', CURRENT_TIMESTAMP);

With firebird 1.5, this insert

insert into v (f1) values ('abc');

creates a new row in T and a new row in LOG. With Firebird
2, it creates a new row LOG and has no effect on T. Is that
what we want?

Regards,


Ann


Subject: Updatable views
Date: 07-Nov-2003 07:11.05
Posted By: Dmitry Yemanov


All,

I'm going to disable the current "feature" when a view can be updatable both
directly and via the trigger. This situation is an anomaly which may cause
server crashes/bugchecks. I suggest the following strong rules:

1) tables are always updatable
2) views with triggers are always updatable
3) views without triggers are updateable only when the well-known
conditions are true (non-union, single table, no aggregates)

Notes:

- Computed fields are non-updatable in all cases but the item 2 above.
- A view with triggers is considered updatable for operation X if it has
at least one BEFORE- or AFTER-X triggers.

Dmitry

H