Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    1

    Unanswered: Insert/Update/Delete Triggers across servers

    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:

    Code:
    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:

    Code:
    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

  2. #2
    Join Date
    Mar 2009
    Posts
    349
    I think you need to create a linked server.

    I broke sum something once with a cross server trigger because there are special rules for distributed transactions and ANSI warnings.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The scope of a trigger should be limited to its own table if possible, and never beyond its own database.
    Find another method of doing this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree wholeheartedly with Blindman, a trigger should be limited in every way possible... The steps are generally:

    1) this row
    2) this table
    3) this database
    4) disaster

    I try to limit my triggers to "this row" and in very, very few cases ever make it to "this database".

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The scope of a trigger should be limited to its own table if possible, and never beyond its own database.
    Find another method of doing this.
    I have read several times on this list, to restrict the use of triggers to validate business rules, and nothing beyond.

    Where can I find some more about the underlying reasons about this best practice? You know a good source for best practices in general?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The reason is a simple matter of scope. Errors generated in triggers can break you application or corrupt your data. You do not want technical issues outside of your database (network, security, bad design, code bugs) to potentially affect your database, and tying triggers to outside systems invites this sort of scenario.
    Triggers are as close to your data as coded business logic can get. So maintain a layer of separation between them and code that is not under you control.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I frequently use triggers for two purposes:
    - updating bookkeeping data (AlterdOn, AlteredBy, AlteredNr)
    - to feed audit tables
    - seldom to enforce business rules, prefer CONSTRAINTs whenever possible

    I was also considering putting the audit tables on another server, as those audit tables can quickly grow quite large.
    But now it seems to be a bad idea to do it with triggers.
    Its probably better to move those records at night from the production server to the audit server.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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