Triggers and DB links
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Triggers and DB links

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

    Question

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

    We work in Pharmaceuticals and teh validation/QA requirements and restrictions mean that any changes to a production system have to be absolutely vital before we expend any great effort and cost doing them.

    Anyway ... we have a production database and our developers want it to automatically update a second database. We can't create triggers in this 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?

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Try creating this trigger in db1.

    [Edited by Raminder on 04-13-2002 at 02:24 AM]

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

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