3

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?

Improve this question
4
  • It would be helpful if you'd edit your question and include the source code of the trigger.
    – Bob Jarvis - Слава Україні
    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/…
    – jim mcnamara
    Jun 5, 2013 at 16:23
  • 1
    belongs on DBA.SE
    – YasirA
    Jun 8, 2013 at 6:31

1 Answer 1

Reset to default
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

Improve this answer
1
  • 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?
    – Andrew Wolfe
    Jun 15, 2013 at 2:45

Your Answer

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

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