When you create a view with check option as below:
recreate view v1 as
select n1
from t1
where n1 < 10
with check option;
Firebird automatically creates triggers as below:
create trigger v1_ins before insert on v1
as
begin
if (not new.n1 < 10) then
exception;
end if;
end!
create trigger v1_upd before update on v1
as
declare dummy integer;
begin
for select 1
from t1
where (t1.n1 = old.n1 or (t1.n1 is null and old.n1 is null))
and t1.n1 < 10
into dummy
do
begin
if (not new.n1 < 10) then
exception;
end if;
end
end!
----------------------
Also you can have fields in WHERE that is not in the field list:
recreate view v2 as
select n1
from t1
where n2 < 10
with check option;
And the triggers will be:
create trigger v2_ins before insert on v2
as
begin
if (not null < 10) then
exception;
end if;
end!
-- This trigger will do nothing!
create trigger v2_upd before update on v2
as
declare dummy integer;
begin
for select 1
from t1
where (t1.n1 = old.n1 or (t1.n1 is null and old.n1 is null))
and t1.n2 < 10
into dummy
do
begin
if (not t1.n2 < 10) then
-- Never will happen
exception;
end if;
end
end!
----------------------
I'm not seeing the need to generate these update triggers as they are now.
If the trigger fires, it means a record was selected and was checked, so
could not only the new value be checked as in the insert triggers?
Adriano
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
