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?
ThanksYour 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|||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.

Last row in select question

DECLARE @.LastID int
DECLARE @.RowsToSelect int

SET ROWCOUNT @.RowsToSelect
SELECT @.LastID = [id] FROM MyTable ORDER BY [id]
SET ROWCOUNT 0

I think that value of @.LastID will be equals to [id] in the last row
of select(i.e. row with number @.RowsToSelect). But I don't understand
why it's true. For example, @.LastID not necessarily equals to last row
value in the same select without 'ORDER BY [ID]'.Kurzman (max@.virtuman.com) writes:
> DECLARE @.LastID int
> DECLARE @.RowsToSelect int
> SET ROWCOUNT @.RowsToSelect
> SELECT @.LastID = [id] FROM MyTable ORDER BY [id]
> SET ROWCOUNT 0
> I think that value of @.LastID will be equals to [id] in the last row
> of select(i.e. row with number @.RowsToSelect). But I don't understand
> why it's true.

Books Online says:

If the SELECT statement returns more than one value, the variable is
assigned the last value returned.

So your observation is correct.

> For example, @.LastID not necessarily equals to last row value in the same
> select without 'ORDER BY [ID]'.

How do you know that it is not the last value? Without an ORDER BY
clause, SQL Server is free to return the rows in any order, so any value
of [id] is correct. Recall that logically tables are unordered sets of
data, so there is no first or last value in a table.

You must always specify an ORDER BY clause to impose a certain order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp