Subject Insert/Update fields not present in a view
Author Urs Liska
Hello list,

I have a question concerning insert/update in views.

[Maybe my first message got lost during my signin process, maybe I
didn't receive any answer with the same reason, so please excuse if this
is a double post.]

The situation is as follows:
1) I have a base table PERSON that stores data about, well, persons.
2) There are subgroups of persons like authors, poets, composers,
3) This is reflected in "boolean" flag fields in PERSON, one for each
4) I would like to see the subgroups as views with a SELECT statement
like "SELECT ... FROM person WHERE author_fl = 1".
I would however like not to see the flag field in the view (the user
should see just the plain author, not also a flag field to eventually
mess with).

For retrieving the subgroup of persons this works well.

The problem arises when inserting or updating into the view. I don't
know how to get access to the flag field of the base table from the view
(i.e. its triggers).
If it was a joined view I could of course (and would have to) execute
insert statements for all tables. It would be no problem to set the flag
field then.
But when I insert in my (updatable) view and insert a record in the base
table (with the flag set), Firebird inserts a second record (without the
flag) directly through the view.

I could see two ways to get around this but didn't find any solution yet:
a)find a way to get hold of a field in the underlying base table that is
not present in the view from within the trigger.
b) manually inserting/updating from the trigger and then tell the
trigger not to insert/update automatically again (some kind of abort).

Does anybody have a tip for this problem?
Or would it be the way to go to just take the flag field into the view
and let applications hide the field from the user?

Thank you in advance