I have been trying to create a trigger which compares values of an update to determine if a field has changed value and if it has, then timestamp the record. The problem is, whenever I try to reference the deleted or inserted tables SQL Server tells me that they are not a valid reference. Any help you could offer would be greatly appreciated. My code is as follows:

CREATE TRIGGER trg_UpdateStatusDate ON dbo.tblName FOR INSERT,UPDATE AS Declare @DeletedCount Integer

select @DeletedCount = ( SELECT Count(*) from deleted )

IF UPDATE (NameStatus) BEGIN if ( @DeletedCount > 0 ) BEGIN if deleted.NameStatus <> inserted.NameStatus BEGIN UPDATE tblName SET StatusDate = GetDate() WHERE (NameID = inserted.NameID) END END ELSE BEGIN UPDATE tblName SET StatusDate = GetDate() WHERE (NameID = inserted.NameID) END END

-- Anonymous, April 13, 1999

Answers

David,

I believe that this is what you want. (I have tested it and it updates the time stamp on each insertion or update of a row.): CREATE TRIGGER trg_UpdateStatusDate ON dbo.tblName FOR INSERT,UPDATE AS

IF UPDATE (NameStatus) BEGIN UPDATE tblName SET StatusDate = GetDate() FROM inserted WHERE (tblName.NameID = inserted.NameID) END

Hope this helps,

Eric

-- Anonymous, April 20, 1999