#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    2
    Rep Power
    0

    Question Use Trigger to Update Data in another DB


    I am new to creating triggers in SQL. My scenario is as follows:

    I have two Databases on the same server - A & B

    When the value of Column X is updated in Table AA of Database A, I need to update the same value(s) of Column Y in Table BB of Database B.

    These tables are linked into an Access front-end and set up as one-to-many (one Column X to many Column Y's), but I cannot set up cascading updates in Access when the tables are linked in. In SQL, these 2 tables come from 2 separate databases.

    How can I create this trigger so that when Column X of Table AA in Database A is updated, the trigger goes to Column Y in Table BB of Database B, checks for the OLD value of Column X and updates found instances with the NEW value of Column X?

    Any help provided would be much appreciated.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    3
    Rep Power
    0
    Does this help?

    Code:
    CREATE TRIGGER trg_YourTrigger ON dbo.AA
    FOR UPDATE
    AS
    UPDATE	B.dbo.BB
    SET	ColumnY = I.ColumnX
    FROM	inserted I
    INNER JOIN
    	deleted D
    ON	I.PrimaryKeyInTableAA = D.PrimaryKeyInTableAA
    WHERE	B.dbo.BB.ColumnY = D.ColumnX
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Posts
    2
    Rep Power
    0
    THANKS - this was very helpful.

IMN logo majestic logo threadwatch logo seochat tools logo