Solved

Last Updated Trigger

Posted on 2003-10-28
2
1,009 Views
Last Modified: 2008-01-16
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!
0
Comment
Question by:3gg
2 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9633506
this is whati do in my triggers:


UPDATE a
   SET LastUpdated=GETDATE()
   FROM MyTable a
      INNER JOIN INSERTED b ON a.ID=b.ID
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 50 total points
ID: 9633517
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
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
Live Storage Vmotion VMs with shared VMDK 10 56
Webservices in T-SQL 3 30
convert null in sql server 12 32
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question