Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Unanswered: Linked Server Trigger

    Hi there,

    I'm pretty new to SQL and am having some porblems with a linked server.
    I have a table on a SQL server which stores employee information.
    I also have a view on a linked server which stores the same information.

    What I would like to happen is, whenever the view changes on the linked server I want the information to be changed in the table on my server.

    I've been trying to write a trigger to do this, but have had no luck so far.

    This is the trigger I've written:

    CREATE TRIGGER fnChangeData

    ON PSREP..SYSADM.PS_ML_UK_EXTR_AMS

    FOR UPDATE

    AS

    SET XACT_ABORT ON

    UPDATE t

    SET
    LAST_NAME_SRCH = i.LAST_NAME_SRCH,
    FIRST_NAME_SEARCH = i.FIRST_NAME_SEARCH

    FROM Employee t JOIN inserted i ON t.EMPLID = i.EMPLID


    And the error I get:

    Server: Msg 117, Level 15, State 1, Procedure fnChangeData, Line 5
    The object name 'PSREP..SYSADM.' contains more than the maximum number of prefixes. The maximum is 2.

    Is this because you can't run a trigger on a linked table? I've tried using openquery as well, but with no success.

    Can anyone help me?

    Much appreciated,


    Simon

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You probably need to write your trigger on the other side, - against the view of your linked server. This means that you have to create a linked server on the other side as well. Also, avoid defaults and fully qualify object references (servername.databasename.owner.object)

  3. #3
    Join Date
    Jul 2003
    Posts
    5
    Problem is I can't create a trigger on the other side, I only have access to it as a linked server.

    Is there any way I can do it from this side?

    Thanks,

    Simon

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    You can create a stored procedure that selects the data from theview on the first server, inserts it on the second one in a temporary table and from that point on you can update the table using the temporary table. Depending on the time you want to allow between an update on the view and the necessary update on the table, you can create a job that will run on interval. It's quite a way around and probably not improving the performance of your server, but you can't fire a trigger from one server when data is changed on another.
    Johan

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pumping the entire set of data from one server to another on a periodic basis maybe...how should I put it?.. You can probably slightly alter the previous reply and have a permanent table on your side. Then with your scheduled task do an INSERT with LEFT OUTER JOIN from the view to your table where the key_field in your table is null.

  6. #6
    Join Date
    Jul 2003
    Posts
    5
    That sounds like a good enough solution to me, thanks.

    I've never written a stored procedure before tho, and could do with some hints?

    Thanks in advance,

    Simon

Posting Permissions

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