Last Updated Trigger

3gg
3gg used Ask the Experts™
on
Hello, I have scripted a trigger and it works, but I am concerned about the overhead of doing it this way. Ideally I would like to do away with the Cursor, but am not sure it can be done for what I am trying to accomplish.  The point of this trigger is to update the dateTime field "lastTouched" to reflect any time information is added or modified.

CREATE TRIGGER update_row_timestamp ON dbo.tableName
AFTER INSERT,UPDATE
AS
 
DECLARE @rowID  int
 
DECLARE curModifiedData CURSOR FOR
SELECT rowID
FROM INSERTED
 
OPEN curModifiedData
FETCH NEXT FROM curModifiedData INTO @rowID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 UPDATE tableName
 SET lastTouched = getDate()
 WHERE rowID = @rowID
 FETCH NEXT FROM curModifiedData INTO @rowID
END
 
CLOSE curModifiedData
DEALLOCATE curModifiedData

In case you need to know, lastTouched is a dateTime, rowID is a primary key on the table.

Thanks for giving this a once over!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
this is whati do in my triggers:


UPDATE a
   SET LastUpdated=GETDATE()
   FROM MyTable a
      INNER JOIN INSERTED b ON a.ID=b.ID
Top Expert 2011
Commented:
no you don't need a cursor...

CREATE TRIGGER update_row_timestamp ON dbo.tableName
AFTER INSERT,UPDATE
AS
 
DECLARE @rowID  int
 
 UPDATE tableName
 SET lastTouched = getDate()
 From TableName as A, Inserted as I
 on A.Rowid=I.Rowid

Return

go

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial