Ask the Expert

Trigger to track database information

I have about 25 databases and 1 "command center" database (CC). My goal is to keep CC informed of any changes made in other databases. The CC contains an application that keeps track of databases, users, their privileges, roles, objects, etc. Currently this task is done manually and is a big loophole. What I would like to do use database triggers to accomplish this. I want to use AFTER DDL statement and within that separate GRANT from REVOKE, CREATE from DROP, etc. How can I do that?

    Requires Free Membership to View

You can create such a trigger. It is not hard to do. But before you create a trigger, you'll need to create a database link to your CC database. On your database server, set up a TNS alias that points to your CC database. You should be able to sign on to the CC database as follows:

sqlplus system/manager@cc

Then sign on to your database and create a database link to the CC database.


You'll be connecting to the CC database with the userid and password you specify. The USING clause is where you tell the database link which TNS alias to use. If the link is set up correctly, then the following will return a value:

SELECT sysdate FROM dual@cc_link;

Next you need to create a trigger that will insert information into a table in your CC database. The trigger can look similar to the following:

CREATE TRIGGER populate_cc
  INSERT INTO change_table@cc_link VALUES ('DDL occurred');

There are many options on such a database trigger, so I would recommend reading the Oracle documentation so that you know what type of triggers to create to meet your requirements.

This was first published in May 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

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

Your password has been sent to: