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,

SimonYou 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)|||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|||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.|||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.|||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