I've had moderate success with placing triggers on rdb$procedures and
rdb$triggers. I'm using these to help me track modifications to procedures and
triggers that are not caused by my "build" system, which auto-builds "default"
procedures and triggers, and that I later customize. (I have it build dozens of
these, with functional but often incomplete code, and come back and do the rest
myself as required.) The idea was that if I customize one of these objects, I
don't want it to get overwritten later on when I ask it to rebuild all the
procedures/triggers/etc. So the triggers I placed on these system tables will
raise an exception if the automated procedure attempts DDL that modifies objects
customized since the last time they were built automatically. *whew*

Tonight I'm having trouble doing the same with views, and furthermore, it seems
that my triggers on rdb$triggers and rdb$procedures randomly disappear.
(Frustrating, as I may not notice they're gone, and my customized code gets
overwritten with default 'dumb' code now and again!) Doesn't seem to happen
every time the database is shut down, I don't think. Certainly not every
transaction. Just ... eventually. I'll probably add some checks to my build, so
it refuses to run if the triggers don't currently exist. Just in case.

I can create the trigger on rdb$relations, and commit. I can then attempt to
create a new view, and I can tell from FlameRobin's logs that
dg_customized_objects was modified -- so my new trigger did certainly fire.
Great. Then I commit -- and the server crashes (segfault.) Everything gets
rolled back, which is nice, but not exactly the outcome I was hoping for.

This is Firebird 2.1.1 PDB SuperServer on win32, running as a service.

PTC-DEV2-XP (Server) Sun Feb 15 23:53:44 2009
Access violation.
The code attempted to access a virtual
address without privilege to do so.
This exception will cause the Firebird server
to terminate abnormally.

I've got BugcheckAbort = 1, but I'm unfamiliar with the process of getting more
info for you guys, nor am I sure it matters -- if I'm in "off-limits" territory ...

Can anyone tell me whether this behavior has been seen elsewhere? Were triggers
on system tables ever intentionally allowed? Are there known issues that could
cause them to disappear seemingly randomly? Did I just code this last one wrong?

For completeness, I've included the code below; my build procedures use
"dg_attempt_ddl", and will fail silently if the objects involved have been
customized.

(Note: the EXECUTE STATEMENT doesn't fail with dg_e_customized, it fails with
sqlcode -607, because the DDL blew up for unknown reasons, though
dg_e_customized does show up in the stacktrace. My original code was wrong, and
I haven't removed that branch.)

Thanks!

-Philip

create table dg_rejected_ddl
(
ddl_statement varchar(32765),
rejected_on timestamp
);

create table dg_customized_objects
(
object_name varchar(50) not null,
customized_on timestamp,
constraint dg_pk_cust_obj primary key (object_name)
);

create table dg_generated_objects
(
object_name varchar(50) not null,
generated_on timestamp,
constraint dg_pk_gen_obj primary key (object_name)
);

create exception dg_e_customized 'Change to object rejected because it has
already been customized';

set term ^;
create or alter procedure dg_prc_protect_objects (o varchar(50), mode char(1)) as
declare variable is_automation integer = 0;
declare variable is_generated integer = 0;
declare variable is_customized integer = 0;
begin
o = upper(rtrim(o));

is_automation = rdb$get_context('USER_TRANSACTION', 'is_automation');
if (is_automation is null) then is_automation = 0;
select 1 from dg_generated_objects where upper(object_name) = :o into
:is_generated;
select 1 from dg_customized_objects where upper(object_name) = :o into
:is_customized;

if (is_automation = 1) then
update or insert into dg_generated_objects (object_name, generated_on) values
(:o, current_timestamp) matching (object_name);
if (is_automation = 0) then
update or insert into dg_customized_objects (object_name, customized_on)
values (:o, current_timestamp) matching (object_name);
if (is_generated = 1 and is_customized = 1 and is_automation = 1) then
exception dg_e_customized 'Change to ' || o || ' rejected because it has
already been customized';
end^
set term ;^

set term ^;
create or alter trigger dg_trg_protect_triggers for rdb$triggers before insert
or update or delete as
declare variable is_automation integer = 0;
declare variable is_generated integer = 0;
declare variable is_customized integer = 0;
begin
execute procedure dg_prc_protect_objects(case when deleting then
old.rdb$trigger_name else new.rdb$trigger_name end, case when inserting then 'I'
when updating then 'U' when deleting then 'D' else null end);
end^
set term ;^

set term ^;
create or alter trigger dg_trg_protect_procedures for rdb$procedures before
insert or update or delete as
declare variable is_automation integer = 0;
declare variable is_generated integer = 0;
declare variable is_customized integer = 0;
begin
execute procedure dg_prc_protect_objects(case when deleting then
old.rdb$procedure_name else new.rdb$procedure_name end, case when inserting then
'I' when updating then 'U' when deleting then 'D' else null end);
end^
set term ;^

set term ^;
create or alter trigger dg_trg_protect_views for rdb$relations before insert or
update or delete as
declare variable is_automation integer = 0;
declare variable is_generated integer = 0;
declare variable is_customized integer = 0;
begin
-- views only
if (inserting and new.rdb$view_blr is null) then exit;
if (deleting and old.rdb$view_blr is null) then exit;
if (updating and (new.rdb$view_blr is null or old.rdb$view_blr is null)) then exit;

execute procedure dg_prc_protect_objects(case when deleting then
old.rdb$relation_name else new.rdb$relation_name end, case when inserting then
'I' when updating then 'U' when deleting then 'D' else null end);
end^
set term ;^

set term ^;
create or alter procedure dg_attempt_ddl (s varchar(32765)) as
begin
-- exceptions not caught by end of procedure cause changes in procedure to be
rolled back
-- ergo, we either unset this flag at the end, or it'll get rolled back by
uncaught exception
rdb$set_context('USER_TRANSACTION', 'is_automation', '1');
begin
log_debug('Attempt', s);
execute statement s;
when sqlcode -607 do begin
log_debug('Metadata change rejected, logging it for later.', s);
insert into dg_rejected_ddl (ddl_statement, rejected_on) values (:s,
current_timestamp); end
when exception dg_e_customized do begin
log_debug('Already customized, logging it for later.', s);
insert into dg_rejected_ddl (ddl_statement, rejected_on) values (:s,
current_timestamp); end
when any do
begin
log_debug('DDL error, sql code is ' || SQLCODE, s);
exception;
end
end
rdb$set_context('USER_TRANSACTION', 'is_automation', '0');
end^
set term ;^

-- log_debug is a UDF that prints stuff to a file on c:\