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.

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?

share|improve this question
    
It would be helpful if you'd edit your question and include the source code of the trigger. –  Bob Jarvis Jun 5 '13 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 '13 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 '13 at 16:23
1  
belongs on DBA.SE –  Yasir Arsanukaev Jun 8 '13 at 6:31
add comment

migrated from stackoverflow.com Jun 8 '13 at 13:43

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

1 Answer

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

share|improve this answer
    
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 '13 at 2:45
add comment

Your Answer

 
discard

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

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