March 24, 2008 at 8:54 pm
I like to track the last time that a row was updated. Most of my tables have a column called UPD_DATE.
I'm coming from another rdbms (Firebird) which allows me to write a before update trigger that will automatically populate that column. How would I do the same thing in Sql Server 2005?
thanks
March 24, 2008 at 9:08 pm
You need to have a column that stores the last change date and use a trigger to ensure it's updated.
alternatively the transaction log will have data in it, but you'd have to dig through it to find the times.
March 24, 2008 at 9:14 pm
With an after update trigger on the table, something like this:
create trigger trgFubar_UPD on Fubar AFTER Update
AS
Update Fubar
Set UPD_DATE = Getdate()
Where Fubar.PK = inserted.PK
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 25, 2008 at 6:21 am
RBarryYoung -
So there is no 'inline' way to update a value before it gets written? Would the update in your trigger cause the same trigger to fire again?
thanks,
Bob M..
March 25, 2008 at 12:39 pm
You're right. If the SET RECURSIVE_TRIGGERS ON option has been set, then it will. You can avoid this by changing the code as follows:
create trigger trgFubar_UPD on Fubar AFTER Update
AS
BEGIN
IF UPDATE (UPD_DATE) = False
Update Fubar
Set UPD_DATE = Getdate()
Where Fubar.PK = inserted.PK
END
Cogiko ergo sum (I geek, therefore I am).
March 25, 2008 at 12:46 pm
BobM (3/25/2008)
RBarryYoung -So there is no 'inline' way to update a value before it gets written? Would the update in your trigger cause the same trigger to fire again?
thanks,
Bob M..
If you have recursive triggers/nested triggers turned on, then you might care to look at making it an INSTEAD OF trigger. As long as you don't change the number of rows you're messing with, you can update things at will without causing it to nest an update trigger.
Of course - you're only allowed ONE instead of trigger per table per action, so use it wisely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 3:05 pm
BobM (3/25/2008)
RBarryYoung -So there is no 'inline' way to update a value before it gets written? Would the update in your trigger cause the same trigger to fire again?
thanks,
Bob M..
The "inline" way to do this is to have the procs that update that table include the update date column.
Personally, I don't like using "created date" and "last updated" type columns. They don't tell you much, and they don't actually describe the entity you are storing in the table.
A better (in my opinion) way to do this is to log the updates, with the update datetime, in a separate database. That gives you not just the last time the row was updated, but a complete history. How often has it been updated? By whom? When was it created? By whom? When was it deleted (if ever), and by whom? Who changed the status of all the orders to "we don't really ship these things, we just like taking money for them"?
If you do use a trigger for this, keep in mind that it means more entries in the transaction log.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2008 at 6:50 am
We include CreateDate, CreatedBy, UpdateDate and UpdateBy columns on our tables which are updated in our update triggers. We also log the record to an audit table after update. The audit table give more information since I can determine which columns where updated but the createdby and updateby columns are most frequently used in queries and when displaying the records. the inserts to the audit table are also in add and update triggers. We have no recursive triggers.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
March 26, 2008 at 8:15 pm
Actually, I find that including CreateDate, CreatedBy, UpdateDate and UpdateBy columns on the table give me the most bang for the buck by far. More than enough information to investigate and diagnose problems, faster and less space than more elaborate schemes, and virtually no management overhead for me.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 26, 2008 at 8:18 pm
David Naples (3/25/2008)
You're right. If the SET RECURSIVE_TRIGGERS ON option has been set, then it will...
Does anyone run with RECURSIVE_TRIGGERS on? It just seems like an insane setting?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 7:19 am
Does anyone run with RECURSIVE_TRIGGERS on? It just seems like an insane setting?
Yes, I do as I have a recursive trigger situation.
First off, I don't normally use triggers for the database side of business logic. Store procedures perform the work. However, in this particular case, the application maintained "linkages" (stored in a separate 1:M table) between records. The master records could be deleted via a CASCADE DELETE rule cased by some other deletion. The specialized logic needed couldn't be done in the main procedure. Therefore, I was forced into a trigger.
To prevent an infinate loop, the trigger just needed some conditional logic to not do things based on a condition. Worked fine in both SQL Server 2000 and SQL Server 2005.
March 27, 2008 at 8:31 am
rbarryyoung (3/26/2008)
Actually, I find that including CreateDate, CreatedBy, UpdateDate and UpdateBy columns on the table give me the most bang for the buck by far. More than enough information to investigate and diagnose problems, faster and less space than more elaborate schemes, and virtually no management overhead for me.
Here's the problem with that scheme:
Joe creates an order in the Orders table: CreateBy = "Joe"
Bob updates the order and changes the price incorrectly: UpdateBy = "Bob"
Sue updates the order and changes the ship date correctly: UpdateBy = "Sue"
The customer backs out of the deal because of the price change, Joe records a $1-million dollar deal went south because of the price being set incorrectly, Sue gets sacked because the database says she's the one who changed the order.
Those columns create an illusion of tracking, without the reality. The "Created" fields are okay, because they don't get updated, but the "Updated" fields are misleading.
And, yes, I've run into this kind of circumstance in real life in real businesses. Not a $1-million deal, but plenty of problems in figuring out what/who needed to change.
Since I track the changes externally to the table, I also track the creation there. It makes for more consistency, and also means I only have to query the audit tables when I create reports on activity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2008 at 12:40 pm
GSquared (3/27/2008)
Joe creates an order in the Orders table: CreateBy = "Joe"Bob updates the order and changes the price incorrectly: UpdateBy = "Bob"
Sue updates the order and changes the ship date correctly: UpdateBy = "Sue"
The customer backs out of the deal because of the price change, Joe records a $1-million dollar deal went south because of the price being set incorrectly, Sue gets sacked because the database says she's the one who changed the order.
The problem with this scenario is that it describes a situation that has nothing to do with the goals that I was referencing, nor with the goals of any worthwhile organization, IMHO. What is portrayed here is a "Culture of Blame" wherein managers and upper-level employees who are incompetent (Account Managers who are not managing million dollar sales & customers, developers who write apps that allow shipping clerks to change Order prices, a DBA who doesn't understand that later Updates overwrite earlier ones, and managers who fire the wrong people for the wrong reasons and encourage this to continue) spend their time trawling for information to allow them to transfer blame from themselves to lower-level minimum-wage employees. That may be your idea of a productive use of your time and skills, but it's not mine.
Audit fields, as I outlined them support the diagnosis and correction of application and database problems and it is far cheaper than other options. It is does not constitute a comprehensive security scheme (C2 auditing does that), nor guaranteed data recovery (backups do that), nor does it support a culture of blame. I have far better things to do with my time.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 12:54 pm
rbarryyoung (3/27/2008)
Audit fields, as I outlined them support the diagnosis and correction of application and database problems and it is far cheaper than other options. It is does not constitute a comprehensive security scheme (C2 auditing does that), nor guaranteed data recovery (backups do that), nor does it support a culture of blame. I have far better things to do with my time.
This is exactly the reason that I use these fields as well. If I need auditing, I fully recognize that these four fields are not going to provide enough information. But if I need to quickly see what time the last update occurred while troubleshooting a problem, they are invaluable.
Great thread guys!
March 28, 2008 at 1:35 pm
rbarryyoung (3/27/2008)
GSquared (3/27/2008)
Joe creates an order in the Orders table: CreateBy = "Joe"Bob updates the order and changes the price incorrectly: UpdateBy = "Bob"
Sue updates the order and changes the ship date correctly: UpdateBy = "Sue"
The customer backs out of the deal because of the price change, Joe records a $1-million dollar deal went south because of the price being set incorrectly, Sue gets sacked because the database says she's the one who changed the order.
The problem with this scenario is that it describes a situation that has nothing to do with the goals that I was referencing, nor with the goals of any worthwhile organization, IMHO. What is portrayed here is a "Culture of Blame" wherein managers and upper-level employees who are incompetent (Account Managers who are not managing million dollar sales & customers, developers who write apps that allow shipping clerks to change Order prices, a DBA who doesn't understand that later Updates overwrite earlier ones, and managers who fire the wrong people for the wrong reasons and encourage this to continue) spend their time trawling for information to allow them to transfer blame from themselves to lower-level minimum-wage employees. That may be your idea of a productive use of your time and skills, but it's not mine.
Audit fields, as I outlined them support the diagnosis and correction of application and database problems and it is far cheaper than other options. It is does not constitute a comprehensive security scheme (C2 auditing does that), nor guaranteed data recovery (backups do that), nor does it support a culture of blame. I have far better things to do with my time.
No need to be rude.
I don't operate in a "culture of blame", nor would I consider such productive. Nobody would.
My point is that, unless it's being used simply to determine when data can be archived, there's absolutely no point at all to an "updated date" field and "updated by" field, other than "auditing". None. And tracking when something can be archived doesn't require an "updated by" field.
On the other hand, if you find that data in your production database has been "messed up", if you have an audit trail that can be used simply to find out who messed it up, it doesn't need to have anything to do with "blame". Maybe an employee needs training. The other option, of course, is to take every employee who might possibly have had any access to the data ever, and train all of them. Even if this means hundreds/thousands of man-hours of training being spent on people who, for the most part, already know how to avoid the problem. The return-on-investment for that "shotgun training" is negative. Find the person responsible, correct their training, a couple of man-hours spent with a positive potential ROI.
There is, of course, another option, which is to simply hold nobody responsible for anything ever. When deals are lost because of errors, when reports cause management to make bad decisions because they're being fed bad data, when people are trained/fired/reprimanded for errors they didn't commit, just say, "oh well, at least we don't have a culture of blame", and go find another employer when that one goes out of business.
There's a huge difference between responsibility and blame.
Of course, this ignores the other advantages to using real tracking, instead of "last modified" "tracking". Things like reports on how long orders take to go through the business, including average time on each sub-step, as tracked by the databases. This gives management the ability to find out which areas are operating more slowly than desired, or where the process is bogging down in back-and-forth, which allows for streamlining.
Using such reports, I was able to work out that certain parts of the order approval process were taking as much as 3-5 days on some orders, and as little as half an hour on other orders. By tracking down the differences in the these, we were able to get the average time down to 1 hour. This just about doubled the company's "bandwidth" for order processing and delivery. All of this was by using the type of logging I'm talking about. Nobody got "blamed". Nobody got fired. There were no negative consequences to anyone. Just a massive increase in revenue over the following months, which ended up being very, very sustainable, and had almost zero increase in cost (a couple of days of my time and a few hours for various managers).
In contrast, what really does "last updated" and "last updated by" give you? Nothing I can think of. Nothing accurate anyway. Nothing that can't be done better by other means.
So, if you have "better things to do with you time" than massively increasing the revenue of your employer, I guess you have better thing to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply