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