Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 oralce 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 (suggested by @APC) is different from other customers (we have limited access to environment),

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

  • Trigger became dissable, and not "just - not - fire",

  • This is not the issue of _system_trig_enabled parameter, @AlexPoole pointed on.

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;
share|improve this question
2  
The question you need to ask is, what is different about this one site where the trigger becomes disabled. My money is on human agency. That is, somebody is deliberately disabling the trigger. –  APC Apr 30 '13 at 8:42
1  
Is it shown as disabled in dba_triggers, or just doesn't fire? It sounds extremely unlikely but there is a hidden init parameter, _system_trig_enabled, which could stop it firing, but it would have other effects that you or the DBAs ought to be aware of. Much more likely that someone is doing it deliberately, as APC says. One possibility is that the client has a security audit tool that doesn't like it and is disabling it automatically, but then that ought to be logged somewhere; just a wild guess... –  Alex Poole Apr 30 '13 at 8:49
    
hmm ... @APC - yes, it is our bets, too. But we should "prove" it to customer, so I want be quite with my self, and looking for alternative reasons. –  evgenyl Apr 30 '13 at 8:54
    
@AlexPoole - thanks for this tip, I have to learn more about it. I'll try to verify any security / audit tools installed. –  evgenyl Apr 30 '13 at 8:54
1  
At the risk of sounding paranoid, this is a DDL trigger, so the rights to meddle with it ought to be held at the DBA level. That is, the same person or persons you're askinbg to audit activity on the trigger. So, if it is a bad actor it will most likely be somebody who will be able to fiddle with the audit trail as well. –  APC Apr 30 '13 at 10:19
show 8 more comments

migrated from stackoverflow.com May 2 '13 at 9:33

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.