Insert/Update/Delete Triggers across servers

• Peter Tijsma

  Valued Member

  Points: 63

  More actions

  May 28, 2009 at 5:28 am

  #72228

  Hi All,

  I'm trying to create a combined Insert/Delete/Update trigger on a table which will update another table on a different server.

  I'm using SQL Server 2000.

  Everything looks fine, but i get the following error when trying to execute a similar select (with server/database prefix) in Query Analyser:

  Server: Msg 117, Level 15, State 2, Line 1

  The number name 'ServerB.DatabaseB.dbo.User' contains more than the maximum number of prefixes. The maximum is 3.

  Below a part of my trigger:

  CREATE TRIGGER User_InsUpdDel ON [dbo].[User]

  FOR INSERT,DELETE,UPDATE

  AS

  Delete from

  [ServerB].[DatabaseB].[dbo].[User]

  Where Exists

  (SELECT * FROM Deleted

  Where

  [ServerB].[DatabaseB].[dbo].[User].[ID] = Deleted.[ID])

  Could someone tell me how to solve this issue ?

  Thanks in advance,

  Peter

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  May 28, 2009 at 9:29 am

  #1001507

  Try changing the delete query to the following.

  DELETEU

  FROMServerB.DatabaseB.dbo.User U

  WHEREEXISTS( SELECT * FROM deleted d WHERE U.ID = d.ID )

  --Ramesh
  

  ---