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

    Question Unanswered: Using a trigger to insert into a different database.

    Hi, does anyone know what syntax I have to use if I want to use a trigger to insert into a different database. This is the code I have I'm just not sure how to get it to identify a different database.

    CREATE TRIGGER History_replication ON [dbo].[History]
    FOR INSERT
    AS
    INSERT [dbo].[History] (this one shoul dbe on the History_copy db)
    (workdone,error)
    SELECT workdone,error
    FROM [dbo].[History]


    Thanks Ed

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    CREATE TRIGGER History ON [dbo].[History]
    FOR INSERT
    AS
    INSERT INTO TargetDatabaseName.[dbo].[History] (workdone,error)
    SELECT workdone,error
    FROM inserted

    Notes:
    I think this works. This is not replication. inserted is a virtual table used by triggers.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd be careful about this. That trigger will be executed every time an insert occurs, so you are essentially tieing the functioning of one database to another. If something goes wrong with database "B", database "A" is going to start throwing errors left and right. Better be prepared to catch and process them.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jul 2003
    Posts
    100
    Thanks unfortunately I think the trigger has crash replication. I was trying to place the final version ofthe trigger on the MSmerge_history and once I have done it everything just shutdown. So this was a failure from the start. Have you every experienced this before?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    if you are using replication between these 2 databases you should'nt need to do this b\c the change should be replicated.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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