I see a message on this list from "Adam" on Sun, 01 Oct 2006 saying that gbak
won't restore triggers on system tables (as part of a discussion of automating
the "SERIAL" column type,) but I haven't been doing backup/restores on this
database. I do have nbackup running regularly though; is there any risk it would
strip the triggers out somehow? Or in case of server crash, would the startup
recovery process (inasmuch as one exists at all) strip them out? I did kill the
server pretty often with bad UDFs, if that explains the disappearing triggers.

-Philip

---------- Original Message -----------
From: "unordained" <unordained_00@...>
To: firebird-support@yahoogroups.com
Sent: Mon, 16 Feb 2009 00:20:31 -0600
Subject: [firebird-support] triggers on rdb$ system tables, access violation

> 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:\
>
>

------- End of Original Message -------