Oracle Database Discussions

We have an application, using ddl database trigger on oracle database.

Trigger depends on package and several tables, package depends on user defined function.

It works on different customer sites well, but for one customer (11g?), sometimes (cannot determine when and why), trigger became disabled.

I know, that trigger can became invalid, when its dependencies changed (i think in this situation Oracle probably try to re-compile it). I understand that it can changed to be invalid when dependencies are not valid.

I know I can alter trigger to became disabled manually.

But when it can became disabled without explicit alter statement be run!? Are there some conditions / circumstance when Oracle changed the trigger to be disabled?

We'll ask the customer DBA team to enable audit on trigger, but it will take time to be done...

Thank you!


Update1:

- We cannot figure it out why this customer environment is different from other customers (we have limited access to environment),

- This happens on several db instances for this customer - no
correlation (time, running sql, ...) found so far,

- Trigger became disable, and not "just - not - fire",

- This **is not** the issue of `_system_trig_enabled` parameter.

Update2:

My trigger code is like

CREATE OR REPLACE TRIGGER MY_SCHEMA.MY_TRIGGER_NAME
BEFORE ALTER OR DROP OR CREATE OR RENAME OR TRUNCATE OR GRANT OR REVOKE
ON database
DECLARE
BEGIN
IF (UPPER(ora_sysevent) = 'TRUNCATE') THEN
MY_SCHEMA.MY_PACKAGE.My_Procedure_1(UPPER(ora_dict_obj_name), UPPER(ora_dict_obj_owner));
ELSE
MY_SCHEMA.MY_PACKAGE.My_Procedure_2;
END IF;
END;

Edited by: Evgeny on Apr 30, 2013 3:11 AM