• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Adding a (calculated) LastModfied column to a frequently updated table

I've working with a table in a legacy database that is updated frequently. I'd like to add a "LastModifiedDate" column to it. The tricky part is the date should only be updated when the values in certain columns change (ie col1 or col5).  There's too much legacy code to modify the UPDATE statements.  So I'm looking for other options.  Triggers come to mind, but I'm concerned it'll cause deadlocks or excessive blocking. Are there any other options? If not, what can be done to minimize the trigger impact?
0
_agx_
Asked:
_agx_
  • 5
  • 5
1 Solution
 
Scott PletcherSenior DBACommented:
A well-coded trigger is by far your best option (in SQL 2005).

That's an easy trigger to write, too.  Code to follow asa I can.
0
 
_agx_Author Commented:
That's okay, I'm familiar with how to write them :) I was just concerned with blocking/deadlocks. Anything I should do to minimize that?

Also, are there even other options? Just for my own knowledge.
0
 
Scott PletcherSenior DBACommented:

CREATE TRIGGER table_name_trg_01
ON table_name
AFTER UPDATE
AS
IF UPDATE(col1) OR UPDATE(col5)
    UPDATE tn
    SET
        tn.LastModifiedDate = GETDATE()
    FROM inserted i
    INNER JOIN table_name tn ON
        tn.keycol = i.keycol --AND tn.keycol2 = i.keycol2
    INNER JOIN deleted d ON
        d.keycol = i.keycol --AND d.keycol2 = i.keycol2
    WHERE
        (COALESCE(i.col1, -1) <> COALESCE(d.col1, -1)) OR
        (COALESCE(d.col1, '<NULL>') <> COALESCE(d.col5, '<NULL>'))

GO

Open in new window

0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
Scott PletcherSenior DBACommented:
Correction:

Of course the last line should be:

    (COALESCE(d.col5, '<NULL>') <> COALESCE(d.col5, '<NULL>'))
0
 
Scott PletcherSenior DBACommented:
>> Also, are there even other options? Just for my own knowledge. <<

Nope, not really.  The only secure way to do this is via a trigger.

You just want to make sure you write the trigger to be as efficient as possible.
0
 
_agx_Author Commented:
Ok, let me give it a whirl.. back in a few.
0
 
Scott PletcherSenior DBACommented:
Efficient = don't create any variable unless absolutely needed, never use a cursor in a trigger unless absolutely forced to -- and even then reconsider, etc..
0
 
_agx_Author Commented:
Don't worry. I can't stand cursors ... this legacy app is full of them and 99% of them aren't even needed.
0
 
_agx_Author Commented:
Works perfectly, thanks.
0
 
_agx_Author Commented:
small typo fix for the archives. change this

  (COALESCE(d.col1, '<NULL>') <> COALESCE(d.col5, '<NULL>'))

to
  (COALESCE(i.col1, '<NULL>') <> COALESCE(d.col5, '<NULL>'))
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now