Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Answered: Log trigger not working.

    I have a trigger that keeps track of status changes...

    IF UPDATE(STATUS)
    BEGIN
    DECLARE @currentdate datetime
    DECLARE @currentstatus integer
    DECLARE @UserID integer
    DECLARE @PermitID integer
    DECLARE @Status integer

    SET @PermitID = (SELECT [PermitCounter] FROM inserted)
    SET @newstatus = (SELECT [Status] FROM inserted)
    SET @currentdate = GETDATE()
    SET @currentstatus = (SELECT Distinct [Status] FROM Deleted)
    SET @UserID = 0

    IF (SELECT COUNT(*) FROM UserTable WHERE [DomainID]=Replace(SUSER_SNAME(),'AZDFBLS\','')) > 0
    BEGIN
    SET @userID = (SELECT UserID FROM UserTable WHERE [DomainID]=Replace(SUSER_SNAME(),'AZDFBLS\',''))
    END

    INSERT INTO PlanLogStatusHistory(PermitID,[DateTime],OldStatusID,NewStatusID,UserID)
    VALUES(@PermitID,@currentdate,@currentstatus,@news tatus,@UserID)
    END

    It works but not the way I want it to. The @currentstatus and @newstatus are the same. I want the status before and after the update. I asked around as to how to do this and some one told me to use the Deleted table. Please help.
    Last edited by AceOmega; 08-28-15 at 13:20.

  2. Best Answer
    Posted by Thrasymachus

    "This trigger will not work if the trigger condition on the parent table (insert/update or delete) impacts more than one record because your code is not set based due to the population of the variables."


  3. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    This trigger will not work if the trigger condition on the parent table (insert/update or delete) impacts more than one record because your code is not set based due to the population of the variables.
    “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.

  4. #3
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Sorry I don't under stand. All really need to know is what I should use instead of DELTED for this line...

    SET @currentstatus = (SELECT Distinct [Status] FROM Deleted)

    to get the Status column's value before it was updated so that I can log it.

    Are you saying that I should have a Where just incase of multiple records like...

    SET @currentstatus = (SELECT Distinct [Status] FROM Deleted where PermitID = @PErmitID)


    Thanks.

  5. #4
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    That worked. Thanks for the input.

Posting Permissions

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