Q

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? 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.

CREATE DATABASE LINK cc_link CONNECT TO user IDENTIFIED BY pass USING
'cc';

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
AFTER DDL ON DATABASE
BEGIN
  INSERT INTO change_table@cc_link VALUES ('DDL occurred');
END;
/

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close