Wednesday, February 25, 2009

Event trigger after logon

This trigger was necessary to execute 1-2 remote selects after login, because all the selects trough db links were working only the third time it was issued, a very strange behaviour. I had to create two similar triggers and they were very effective until we found the root cause.

CREATE OR REPLACE TRIGGER toni_bdn AFTER LOGON ON database
DECLARE
v_user VARCHAR2(30); x number;
a integer; b integer; c integer; a1 integer; a2 integer; a3 integer;
b1 integer; b2 integer; b3 integer;
BEGIN
if sys_context('userenv','session_user') in ('SYSTEM','SYS','BDNOWN01','BDNUSR01') then
a:=dbms_sql.open_cursor;
dbms_sql.parse(a, 'select count(1) from midual@BDN_DL_ROMEGA', dbms_sql.native);
execute immediate 'select count(1) into x from midual@BDN_DL_ROMEGA';
b:=dbms_sql.execute(a);
b1:=dbms_sql.execute(c);
b2:=dbms_sql.execute(a);
b3:=dbms_sql.execute(a);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end if;
END;
/

No comments: