trigger problem

hi
i created a trigger to block developers using TOAD on Production db. following is the trigger:

CONNECT / AS SYSDBA; CREATE OR REPLACE TRIGGER block_toad_from_prod   AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog FROM sys.v_$session   WHERE audsid = USERENV('SESSIONID')
    AND audsid != 0 -- Don't Check SYS Connections     AND rownum = 1; -- Parallel processes will have the same AUDSID's

  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' THEN

     RAISE_APPLICATION_ERROR(-20000, 'Toad users not allowed on PROD DB!');
  END IF;
END; It works fine and does what it was created for.But the problem is that i also have multimaster replication on prod db..and the repadmin jobs are faling due to this trigger..i saw the error in alert file as the jobs were continously failing...it was giving me error at recursive sql statement..

so is there some problem with trigger....or any other way to write it.

regards Received on Thu Apr 14 2005 - 06:34:01 CDT