Subject Re: [firebird-support] Updating base table fields not present in a view
Author Ann W. Harrison
Urs Liska wrote:
> When creating the SELECT statement for the views I include a clause like
> 'WHERE AUTHOR_FL = 1' and it selects the right rows.
> Now the problem is: if the flag field is not in the VIEW declaration,
> (how) can I tell a TRIGGER to set this field in the underlying base table?

You'll need insert, update, and delete triggers for each view (or
combined triggers, depending on the version of Firebird). Each trigger
performs the requested operation on the base table using the new or old
values for the view. You'll also need to keep the view from being
directly updated.



e.g. (plus or minus typos and brain fade)

create table users (user_type char(1), user_name varchar (30));

create view a_list as select user_name from users u
join rdb$database d
where u.user_type = 'A';

create trigger a_list_insert for a_list before insert as begin
insert into users (user_type, user_name) values 'A', new.user_name;

create trigger a_list_delete for a_list before insert as begin
delete from users u
where u.user_type = 'A' and u.user_name = old.user_name;