I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review.

The challenge is that my trigger logic is stored in the same schema on which I've put the trigger. Because of this, whenever I recompile the trigger or the package or whatever, the trigger itself fires. Also, if another component requires a recompile, the trigger fires.

The trigger has been failing and, in fact, locking my database session. I have started putting guards on the trigger to prevent this and haven't worked it out yet.

Perhaps someone on the site has experience or advice? Or just tell me to use AUDIT commands instead?

  • It would be helpful if you'd edit your question and include the source code of the trigger. Jun 5, 2013 at 15:13
  • audit is doing the exact same thing. i don't see any reason why not us audit to track DDL operations. have you tried it and your not happy with it ?
    – haki
    Jun 5, 2013 at 15:44
  • 1
    Generally this is not really doable because ddl does not always generate events. - but here is along discussion on Ask Tom about ddl triggers. Please read on to the bottom to get modern db information: asktom.oracle.com/pls/asktom/… Jun 5, 2013 at 16:23
  • 1
    belongs on DBA.SE Jun 8, 2013 at 6:31

1 Answer 1


Why do you want to log DDL on your DB? just for auditing? a trigger is highly not recommended because that if you have an error you can't even disable the trigger. consider this scenario:

  1. Trigger fails and cashes all DDL operation on the table
  2. You try to disable the trigger
  3. The trigger is invoked (back to step 1)

I suggest you'll take a look at Oracle DB vault and audit vault http://docs.oracle.com/cd/E14472_01/doc.102/e14459/toc.htm

It got some really nice features for monitoring and controlling administration commands

  • Thank you - I was living that scenario! But there might be a way to make DDL triggers less recursively destructive. I mean, there ARE DDL triggers and somebody has to make it work somehow. Or do you have to apply them to a schema other than the trigger's schema (requiring CREATE ANY TRIGGER priv I guess)..? More ideas? Jun 15, 2013 at 2:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.