Trigger: AFTER CREATE ON SCHEMA doesnt fire

Trigger: AFTER CREATE ON SCHEMA doesnt fire. It used to work but stopped working one weekend. The code below works correctly on 2 out of 3 instances I have running. But for some reason stopped working on the 1 instance. Are there any DB parameters that effect the operation of this trigger? The 'debug' table below remains empty after running any CREATE OR REPLACE DDL.

CREATE OR REPLACE TRIGGER ac$schema
AFTER CREATE ON SCHEMA
DECLARE
ls_err VARCHAR2(200) := NULL;
BEGIN
INSERT INTO DEBUG (timestamp,src,msg)
VALUES (SYSDATE,'ac$schema trigger',
SYS.dictionary_obj_type||'-'|| SYS.dictionary_obj_name); EXCEPTION
WHEN OTHERS THEN
ls_err := SQLERRM;
INSERT INTO DEBUG (timestamp,src,msg)
VALUES (SYSDATE,'ac$schema trigger',ls_err); END;
/

CREATE TABLE debug
(timestamp DATE,
src VARCHAR2(32),
msg VARCHAR2(1000))
/

• this should cause the trigger above to fire and insert into debug. CREATE procedure ac$schema_trigger_test as begin null; end;
  /
  

Thanx for any help!
Jim Anderson
San Diego, CA Received on Thu Sep 16 2004 - 18:57:50 CDT