Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: lastUpdatedTs and userID from Inserted table

    I wrote the following trigger:

    CREATE TRIGGER dbo.tru_Employee
    ON Employee
    AFTER update
    AS

    DECLARE @lastUpdatedTs datetime
    DECLARE @table NVARCHAR(50)
    DECLARE @transID INT
    DECLARE @userID INT

    SET @table='Employee'

    SELECT @userID = lastUpdatedBy,
    @lastUpdatedTs=lastUpdatedTs
    FROM inserted

    INSERT INTO AUD_tracking VALUES(@table,@lastUpdatedTs,@userID)
    SET @transID=@@IDENTITY


    Every time this triger fires it always reads the same value for userID and lastUpdatedBy. It's like 'inserted' table is not even affected by my UPDATE statement while @oldvalue and @newvalue that I populated the other table with (Tracking details table) are just fine.

    What could it be?
    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your trigger fires once for each operation, even if many records were updated. Try this more common syntax:

    INSERT INTO AUD_tracking (Table, LastUpdatedTs, UserID)
    SELECT 'Employee', LastUpdatedTs, LastUpdatedBy
    FROM Inserted

    Tip: I like to add a field to my audit table where I can store the Primary Key of the affected records so that I also know what was altered. Sometimes, I even store the before and after values. It makes a thorough paper trail that has proven helpful in diagnosing problems and averting blame.

    blindman

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    Originally posted by blindman
    Your trigger fires once for each operation, even if many records were updated. Try this more common syntax:
    Thank you for your reply.

    I am sorry for giving a confusing information. This was only a beginning part of the trigger. It is followed by the part for updating each and every column. Also I am creating two tables: one that I already mentioned and the other one with tracking details(oldvalue, newvalue, etc.).

    As for the timestamp amd userID being the same, I just noticed that lastUpdatedTs and lastUpdatedBy doesn't change on update in the original table either. At least when I simply run update with QA.
    So I guess I have to go back to this on Monday.

Posting Permissions

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