Hi
I need to find out, which user/using which query/using which application is occasionally deleting records in my_table. (there are lot o queries in few different programs connected to same db). I've wrote a before_delete trigger as follows:


CREATE OR ALTER TRIGGER MY_TABLE_BD0 FOR MY_TABLE
ACTIVE BEFORE DELETE POSITION 0
AS
declare variable temp_sql varchar(10000);
begin
/* there can be more than one active stmt */
for select longc(STMT.MON$SQL_TEXT)
from MON$STATEMENTS STMT
where STMT.MON$ATTACHMENT_ID = current_connection
and STMT.MON$STATE = 1
into :temp_sql
do
temp_sql = temp_sql||'; '||longc(temp_sql);

insert into "LOG" (
log_id,
dtime,
ip_and_prog,
active_sql)
values (
gen_id(gen_log, 1),
'now',
(select MON$REMOTE_ADDRESS||'; '||MON$REMOTE_PROCESS
from MON$ATTACHMENTS
where MON$ATTACHMENT_ID = current_connection),
:temp_sql
);
end

... and it works perfectly using ibexpert - active_sql contains 'delete from my_table mt_ibe where mt_ibe.id = ?. When using TIB_query in application, active_sql is empty or some other statements are written - there is no delete statement. Why?

Regards, Tomek