Triggers and Distributed Databases
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Triggers and Distributed Databases

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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
    Share on Google+

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    You cannot change the structure of a DB without having a connection to that DB.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com
    Share on Google+

  3. #3
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    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




    A Wise Man Knows How much he doesn't know !!!
    Share on Google+

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Julian, I'm not sure I follow you ...

    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?
    Share on Google+

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Julian, I'm not sure I follow you ...

    Yes, they should be in the same DB.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com
    Share on Google+

  6. #6
    Join Date
    Feb 2001
    Posts
    389
    why not use logminer .
    Share on Google+

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width