Triggers and Distributed Databases

[JMac]

I've got a problem with a new requirement from the developers.

We work in Pharmaceuticals and the validation/QA requirements and restrictions mean that any changes to a production system have to be absolutely critical before we expend any great effort and cost doing them. And tehn we spend months re-validating the system ...

Anyway ... we have a production database and our developers want it to automatically update a second database. We can't create triggers in the Production Database due to the QA restrictions.

I've tried to think laterally and have created a link from the new Database (DB2) to the production database (DB1).

I tried creating a trigger in DB2 that fired when a table in DB1 was updated. I wasn't sure whether this was possible but gave it a go anyway. The triggering statement was that AFTER INSERT ON tablename@db_link_name(DB1)
FOR EACH ROW
BEGIN
INSERT INTO table_in_DB2 ... etc

This trigger creation failed with ORA-02021: DDL operations are not allowed on a remote database.

Now I know this error can be caused by a variety of things and occurs for other reasons.

Is what I am attempting intrinsically wrong (ie) impossible?

I wasn't convinced it was possible when I started so can someone convince me either way? Or suggest an alternative?

I'm running 8.1.5 by the way

[julian]

You cannot change the structure of a DB without having a connection to that DB.

[virajvk]

Dear sir ,

I have also tried to create such triggers but I came to conclusion that Trigger are database object specific meaning on remote database we can not put trigger.

But in any DML trigger we can refer to remote database for dml operations.

bye
viraj
virajvk@hotmail.com

[JMac]

Julian,
Not sure what you mean.

In my eyes the reason this fails is because it is impossible to create a trigger in DB1 that is 'triggered' by an action in DB2. It seems logical to me that Trigger and Table must be in the same database. Is this what you mean?

[julian]

Yes, they should be in the same DB.

[gpsingh]

why not use logminer .

[marist89]

Duplicate thread, closing... http://www.dbasupport.com/forums/sho...threadid=23579