I have a stored procedure to selectively log all changes to any table. It logs correctly, however it is logging when it is not supposed to be logging, that is, it continues to log changes to a table even though the option to log that table is not set to 'yes'.

I have set up the table-change log as follows:

* After update, insert, delete triggers for a table that send the table name, record key, and change type ('I, 'D', 'U') to a stored procedure. These 3 triggers are defined on any table that may require logging. A separate 'table options' table is maintained, storing table names and a 'logging' boolean field. The idea is that you can selectively switch on table-change logging for any table set up for logging;

* The stored procedure that receives the above values. First the SP looks up the table name in a 'table options' table. If a field called 'logging' is set to 'Y' for that table name then the procedure continues, otherwise it exits (well, it is supposed to). Finally the procedure inserts the passed info into a 'changes log' table.

The problem is, the SP does not seem to be exiting where it should. I tested the whole shebang without entering any records into the 'table options' table (no tablenames, no 'Y' flag), which should have meant that my test table changes were not logged -- however they were!

I'm hoping it's just a dumb error on my part with the use of variables in the SP. Can anyone take a quick look at the code and point out any obvious mistakes. Much appreciated. Metadata follows (longish, sorry).

----------
The test table (the one being logged):

CREATE TABLE "TEST1"
(
"F_DATE" DATE default 'now',
"F_TIME" TIME default 'now',
"F_PK" "DOM_PK",
"F_RECGENSTAMP" "DOM_RECGENSTAMP",
"F_MEMO" BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET ISO8859_1
);

CREATE TRIGGER "TRIG_TEST1_3" FOR "TEST1"
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.f_pk = gen_id(gen_pk, 1);
end
^

CREATE TRIGGER "TRIG_TEST1_LOGGING2" FOR "TEST1"
ACTIVE AFTER INSERT POSITION 0
as
begin
execute procedure sp_log_changes
('TEST1',new.f_pk, 'I');
end
^

CREATE TRIGGER "TRIG_TEST1_LOGGING1" FOR "TEST1"
ACTIVE AFTER UPDATE POSITION 0
as
begin
execute procedure sp_log_changes
('TEST1',new.f_pk, 'U');
end
^

CREATE TRIGGER "TRIG_TEST1_LOGGING3" FOR "TEST1"
ACTIVE AFTER DELETE POSITION 0
as
begin
execute procedure sp_log_changes
('TEST1',old.f_pk, 'D');
end


----------
Table "changes_log" (records that changes to any table):

CREATE TABLE "T_CHANGES_LOG"
(
"F_USER" VARCHAR(48) CHARACTER SET ISO8859_1 default current_user NOT NULL,
"F_RECGENSTAMP" TIMESTAMP default 'now' NOT NULL,
"F_TABLENAME" VARCHAR(25) CHARACTER SET ISO8859_1,
"F_RECORDKEY" NUMERIC(18, 0),
"F_TYPE" CHAR(1) CHARACTER SET ISO8859_1
);


----------
Table "table_options" (note, this table is empty!):

CREATE TABLE "T_TABLE_OPTIONS"
(
"F_TABLENAME" VARCHAR(25) CHARACTER SET ISO8859_1 NOT NULL,
"F_LOGGING" CHAR(1) CHARACTER SET ISO8859_1,
PRIMARY KEY ("F_TABLENAME")
);


----------
and the faulty procedure "log_changes":

CREATE PROCEDURE "SP_LOG_CHANGES"
(
"VAR_TABLENAME" VARCHAR(25) CHARACTER SET ISO8859_1,
"VAR_KEY" NUMERIC(18, 0),
"VAR_CHANGETYPE" CHAR(1) CHARACTER SET ISO8859_1
)
AS
declare variable var_logging char(1);
begin
select f_logging
from t_table_options
where f_tablename = :var_tablename
into :var_logging;
if (:var_logging <> 'Y') then exit; /* <--- why not exiting here? */
insert into t_changes_log
(f_tablename, f_recordkey, f_type)
values
(:var_tablename, :var_key, :var_changetype);
end
^

[Non-text portions of this message have been removed]