Row Audit fields (Created/Modified) using Computed Columns

Question

Row Audit fields (Created/Modified) using Computed Columns

jjerome00

Say Hey Kid

Points: 706
More actions
June 21, 2010 at 1:02 pm

#225882

On a number of our tables, we keep some light audit information about each row: Active (yes/no), Created_date, Modified_date, Inactivated_date, etc. We keep this information up to date using default values and triggers.
I was thinking the other day that maybe we could use a persisted computed column for something like the Modified date. If I set the column "Modified_date" as a persisted computed column with the value "getdate()", that the date would only be updated when the record itself was updated.
I tried setting up a test case, but SQL Server 2005 says that I can't use getdate() for a computed column (it's non-deterministic).
I was just wondering if anyone else has tried this and been successful with it, or if we should just continue using triggers.
Thanks for your time!

Viewing 6 posts - 1 through 5 (of 5 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Since a persisted computed column is actually stored in the target table as opposed to a plain computed column that is virtual - why not use a standard column and a default value?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95461 More actions · Answer 2

I was thinking the other day that maybe we could use a persisted computed column for something like the Modified date. If I set the column "Modified_date" as a persisted computed column with the value "getdate()", that the date would only be updated when the record itself was updated.

Even if you could do this, there's a flaw here. A computed column is calculated whenever the row is accessed, so running a select (say, for a report) would show the wrong value - it wasn't actually modified then, but it's being reported as such.

IMO, you'd be better off with a regular column that is updated by an UPDATE,INSERT trigger. A default value wouldn't be necessary, since the trigger would do always maintain it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

WayneS (6/23/2010)
IMO, you'd be better off with a regular column that is updated by an UPDATE,INSERT trigger. A default value wouldn't be necessary, since the trigger would do always maintain it.

Hi Wayne -

Please correct me if I'm wrong.

1- Certain operations do not fire triggers by default - like bulk insert therefore by using a trigger we are adding some level of future uncertainty to the quality of the data.

2- Executing a trigger involves loading, parsing and executing a piece of code while default value is embedded functionality therefore default value should be less expensive in terms of performance.

Why don’t use a default value when what is needed is a default value?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95461 More actions · Answer 4

Hi Paul,

#1: yes, you're correct, I didn't think about bulk loads.

#2: A default is only used on the initial insert into the table for that row. I was thinking about the "modified date" column (as the OP was talking about). For this, when it's updated, it would need a trigger to update the current value to the latest value.

So, to revise:

For inserts, use a default constraint.

For columns that need to be updated on an update to the row, use a UPDATE trigger.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

You are correct. Thank you Wayne.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.