Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: A simple trigger that doesn't work

    Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...

    I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.

    If anyone could help me, I would appreciate.

    NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?

    Francois

    This is the trigger:
    ------------------------------------------------------------

    ALTER TRIGGER UStatus
    ON AccesCard
    AFTER UPDATE, INSERT
    AS

    DECLARE @noPerson int

    SET NOCOUNT OFF

    IF UPDATE(LastMove)
    BEGIN
    SELECT @noPerson = Person FROM INSERTED
    UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
    END

    SET NOCOUNT ON

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you are making the assumption that you are only updating 1 record at a time. triggers need to handle sets. think in joins.
    “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
    You would need to set up a linked server connection to do this. And DON'T DO IT! This is one of the worst things you can do with a trigger.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    May 2005
    Posts
    48

    Solution.

    --Use the following code. You update should handle multiple rows simultaneously. However, in case you DB exist on a different server, set up linked server first:

    ALTER TRIGGER UStatus
    ON AccesCard
    AFTER UPDATE, INSERT
    AS

    SET NOCOUNT OFF

    IF UPDATE(LastMove)
    UPDATE A
    SET A.status = 1
    from Intranet.dbo._Users A, Inserted I
    WHERE A.personNo = I.Person

    SET NOCOUNT ON

    ---Scalability Experts.

  5. #5
    Join Date
    Jan 2006
    Posts
    3
    Hi all, first of all, thanks for your helpful advices, I greatly appreciate it. The trigger is now working properly.

    How did I solve the problem:
    As a lot of people suggested me to do, I changed some lines in the trigger to make it compatible with multiple insert or update statements and change the security contex. I added specifics users from the source DB on the target source.

    Thanks all for your help ! Francois

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps the reason they are called "triggers" is because they make it so easy to shoot yourself in the foot....as you seem bound and determined to do after SQLDBA_2005 so kindly loaded the gun and showed you where to point it.

    Just my opinion.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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