LastUpdate Date/time stamp

Question

LastUpdate Date/time stamp

BSavoie

SSCertifiable

Points: 6309
More actions
July 16, 2010 at 11:53 pm

#221358

How can I add a column to an existing table that tracks the last time the row was updated, and this is the important part, without touching client apps. I now I can add a DateTime column to the table with a default of GETDATE(), but that doesn't help me for updates. I can think of a couple ways to do it with trigger code, but something tells me you guys will have a better way. You always do! 🙂
Thanks!
.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

mferanda SSC-Addicted Points: 489 More actions · Answer 1

For ease, I believe a trigger is the route you should go. You can trigger it on update without any modifications to end users or applications.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

You should be using stored procedures anyway. Simply update the new column to GETDATE each time you do an insert/update.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

I agree with bt if you can rewrite all code to use stored procs.

If you want to be 100% sure, or can't change code, use a trigger. Be sure you write this trigger to work with multiple rows and don't store values in variables.

BSavoie SSCertifiable Points: 6309 More actions · Answer 4

Trigger it is then. Thanks

In this particular case, I'm trying to work around frozen code.

.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

Keep the trigger small and tight to avoid performance stuff, and make sure you write the trigger for multiple rows. Don't set variables to values.

Not this:

...

select @pk = PKID from inserted

update BaseTable

set Lastdate = getdate()

where PKID = @pk

Do this

update BaseTable

set LastDate = Getdate()

from inserted i

where BaseTable.PKID = i.PKID

BSavoie SSCertifiable Points: 6309 More actions · Answer 6

Got it. Thanks Steve! You saved me the embarrassment of asking for the code 🙂

I'm not sure I understand the penalty of using local vars in the trigger. Why is the local var version so bad?

.

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 7

Hi,

Steve points to an important point.

The important point is variables work successfully only the original update code affects single table row.

If you use variables just it is the case in the first example, you can only update 1 row.

I guess it is the last row that is updated.

But Update statements will possibly update a batch of rows in the table.

Like below which updates all rows

UPDATE TableName SET FieldName = 'FieldValue'

So your trigger must handle this case too.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

BSavoie SSCertifiable Points: 6309 More actions · Answer 8

Oh, right! I get it. I understand the multiple rows thing. I learned that lesson many years ago. I thought the point was that local vars are BAD in a trigger, but I missed the point all together.

Thanks!

(And the trigger is working spectacularly btw!)

.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 9

Glad it's working, and you are welcome.

I believe some other products fire the trigger for each row, so you have to know your RDBMS. This is a common issue that people run into in SQL Server, and I don't think it's explained well in BOL. Happy we could help you.

HowardW One Orange Chip Points: 29109 More actions · Answer 10

Joe Celko (7/21/2010)
Get a third party audit tool. If the law catches you keeping audit data in the same table, you can go to jail.

Eh? Where did he mention that this was for auditing purposes. You don't even know anything about the application, what information it holds, what country he's in etc. Even if it was for auditing purposes, that's a massive overstatement. It's also entirely sensible to hold a last_updated date in nearly every table - you just hold audit information externally as well if it's appropriate to the application and your auditors requirements.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 11

Joe Celko (7/21/2010)
Get a third party audit tool. If the law catches you keeping audit data in the same table, you can go to jail.
...
.

Compare prices on amazon 😀 and you might consider getting a good lawyer instead of tool. Also, check the state laws where you are in, it could be that keeping audit data in "illegal" way may attract corporal or even capital punishment (caning, flogging or some varieties of stoning) - :w00t:

Be ALERT!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 12

But I would recommend u to create another table with foreign key, n that will hold lastupdated column. Reason behind is, to make sure that it wont ask to make any changes in the existing application. n Definitely using triggers to update it would be a good choice from my point of view.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 13

One advantage of another table is you get some "history" as well. You can see who changed it when. If you do this, you might also think about periodically archiving this data off to another db over time as it can get lengthy

BSavoie SSCertifiable Points: 6309 More actions · Answer 14

The audit table is overkill in this case. If I did need that much detail, doesn't sql2k8 have some built in autiting?

(Somebody needs a nap. 🙂 )

.