May 3, 2010 at 11:25 pm
I have added one linked server name as [linked_server]
now
i have created a trigger in my Server as [local_server]
My trigger is
CREATE TRIGGER REMOTE_TESTING ON TEMP
FOR
UPDATE
AS
BEGIN
SET NOCOUNT ON
EXECUTE SP_EXECUTESQL "UPDATE [linked_server].MASTER.DBO.[TEM] SET NAME =
(SELECT TOP 1 NAME FROM TEMP WHERE TS = (SELECT MAX(TS) FROM TEMP))"
SET NOCOUNT OFF
END
when i executing the above trigger i got the error
Msg 8197, Level 16, State 4, Procedure REMOTE_TESTING, Line 1
The object 'TEM' does not exist or is invalid for this operation.
Is it possible to trigger the trigger in between linked server
May 4, 2010 at 1:26 am
It is possible to trigger a trigger..
Does the table TEM exist on the linked server at: MASTER.DBO.[TEM]
Also, is the remote server case sensitive?
I have to say that using a trigger to update a remote server is problematic. For this particular example the trigger would fire once no matter how many rows were updated, I don't know if that is the desired action. Also on the local side the update will not complete until the updateon the remote object is completed. This could account for significant latency.. Does that update HAVE to occur at exactly that same moment? If not another technology such as Service Broker might be a better fit.
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply