Problem solve Get help with specific problems with your technologies, process and projects.

Logging user changes in audit trail tables via trigger

We have several sensitive tables for which we need to maintain an audit trail when data is inserted, modified, or deleted to comply with Sarbanes-Oxley. We have created tables to contain the changes along with the name of the person making the changes. We are using triggers on the sensitive tables to log the changes. Is there a way to allow users' changes to be put into the audit trail tables via the trigger but not be updated directly?

If I understand the question correctly, you wish to find a way to have a trigger perform an action that indirectly causes a row to be inserted into an audit table, but not perform the insert in the trigger directly.

I'm not sure exactly what your reasons are for doing something like this unless your audit tables will reside outside of the database where the audit records are generated. Regardless, I think the best solution for this may be to create a queue in the source database and have the trigger enqueue a message that is then dequeued by a procedure that generates the audit records. Advanced Queues have been available for quite a while now. I think there's even an entire documentation book on the feature in the standard documentation set for 9.2. I'd start there.

Dig Deeper on Oracle database security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.