Create a procedure to audit changes done in a database

DELETE statement
SQL development
I want to create a procedure it should be able to audit any change done in the database ie. drop/delete of schema tables. the procedure should be able write audited data into a remote server machine inaccessible by the system manager/dba.

Software/Hardware used:
i m using sql developer, 9i database. and windows xp oprating systim

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to read about triggers. Truggers ae procedures that fire when something is done with an associated table — like insert, update, or delete. You are prsented the state of the data at the time (in the case of updaes — both before and after) so it easy to make such an audit trail. tSQL stored procedures can reach other databases on the same server easily. And, tables in databases on other servers by linking the servers. The only consideration is that you need to be certain your code will not fail (because it is unexpected in the normal users code) and that it does take a lot of time — poorly written triggers can cause serious delays in bulk insets and updates… There are many sources of free information about triggers in tSQL. You might want to start with a Google search…


Although the above answer mentions t-Sql and the question is about Oracle, the trigger concept is the same, but if you want to log changes to schema objects (and not DML operations to specific objects like inserts/deletes/updates), you should look at DDL triggers, which can be created at the database level or the schema level.

As for writing this data to a remote server, you would have to create a database link (if the data is going to be stored in a database). Making this inaccessible to the DBA is a matter of privileges/permissions (which are usually managed by the DBA…).


Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • The most-watched IT questions this week: March 30, 2010 - ITKE Community Blog
    [...] create a procedure, asked by Shwetamishra and answered by Carlosdl [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: