July 16, 2010 at 11:53 pm
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!
.
July 19, 2010 at 1:20 pm
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.
July 19, 2010 at 2:43 pm
July 19, 2010 at 2:47 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 19, 2010 at 10:58 pm
Trigger it is then. Thanks
In this particular case, I'm trying to work around frozen code.
.
July 20, 2010 at 8:33 am
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 20, 2010 at 10:39 pm
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?
.
July 20, 2010 at 11:10 pm
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.
July 20, 2010 at 11:17 pm
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!)
.
July 21, 2010 at 9:27 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 21, 2010 at 10:18 am
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.
July 21, 2010 at 10:20 am
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!
July 21, 2010 at 10:35 am
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.
July 21, 2010 at 11:22 am
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 21, 2010 at 8:17 pm
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. 🙂 )
.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply