Update Trigger on multiple columns

Question

Update Trigger on multiple columns

M Joomun

SSCrazy

Points: 2018
More actions
March 10, 2011 at 3:24 am

#234963

Hello All,
I'd like to create an update trigger to detect changes to multiple columns. I can create a trigger to append an entire row of data to an audit table after an update on the target table. But this a pretty inefficient way of going about things and I was wondering if it's possible have a trigger that would only write the data that have changed, together with the column name(s) to my audit table.
Also, I'm keeping different audit tables; one for updates, another for deletes etc. Is this the best way of doing things?
Thanks for any help you can give.
March 10, 2011 at 7:52 am

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

M Joomun (3/10/2011)
Hello All,
I'd like to create an update trigger to detect changes to multiple columns. I can create a trigger to append an entire row of data to an audit table after an update on the target table. But this a pretty inefficient way of going about things and I was wondering if it's possible have a trigger that would only write the data that have changed, together with the column name(s) to my audit table.
Also, I'm keeping different audit tables; one for updates, another for deletes etc. Is this the best way of doing things?
Thanks for any help you can give.

Before you even think of capturing only the changes and the columns names, very carefully consider how you would put a row "back together" if you ever needed to do such a thing. In fact, I'd recommend writing that code long before writing the audit code to support a "changes only" audit table just in case you change your mind.;-)

So far as keeping different tables for Updates/Deletes, etc, consider just one table with a single character column to represent the action taken.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

M Joomun SSCrazy Points: 2018 More actions · Answer 2

Firstly, thanks to both of you for the replies.

Jeff Moden (3/10/2011)
Before you even think of capturing only the changes and the columns names, very carefully consider how you would put a row "back together" if you ever needed to do such a thing. In fact, I'd recommend writing that code long before writing the audit code to support a "changes only" audit table just in case you change your mind.;-)

Jeff, are you saying it's best to save the entire row of data after an update rather than column(s) which have been saved?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

M Joomun (3/11/2011)
Firstly, thanks to both of you for the replies.
Jeff Moden (3/10/2011)
Before you even think of capturing only the changes and the columns names, very carefully consider how you would put a row "back together" if you ever needed to do such a thing. In fact, I'd recommend writing that code long before writing the audit code to support a "changes only" audit table just in case you change your mind.;-)
Jeff, are you saying it's best to save the entire row of data after an update rather than column(s) which have been saved?

I'm saying "It depends". If you save only the changes, they end up going into an EAV (Entity, Attribute, Value) table or at least it's little brother, an NVP (Name, Value Pair) table as you've suggested. That means that the "Value" column in both must be in some form of character format and it has to be wide enough to handle any and all values.

That being known, ask yourself...

1. How much of a load will the audit trigger that saves just the changes along with the required conversion put on the system especially on frequently updated tables?

2. How will I put the "changed only" data back together for any point in time? How long will it take to write and then use the code that does that?

3. How many additional columns of data do I need to capture such as datetime of the mod, who changed it, and from where? Then ask if you're really saving that much storage.

4. Am I going to save the original INSERT (usually a BIG mistake because it will more than double your disk space requirements)

If you have easy answers for the above, then by all means, use the "change only" method of auditing.

What I've found in practice is that it's, many times, just easier, faster, and more effective in the long run to do the following.

1. Don't audit the original insert. There's no need because it lives in the original table. I it hasn't been changed, nothing will appear in the audit log and that's a simple join if you want to combine the two.

2. Audit full rows because normally much more than 1 column is updated which eats up the supposed savings of a "change only" audit log.

3. Full row audits give you a super easy "point in time" audit trail. No fancy code required.

4. Full row audit triggers are much easier to write and will frequently be faster than the dynamics required to find and save only what changed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

M Joomun SSCrazy Points: 2018 More actions · Answer 4

Jeff Moden (3/11/2011)
M Joomun (3/11/2011)
Firstly, thanks to both of you for the replies.
Jeff Moden (3/10/2011)
Before you even think of capturing only the changes and the columns names, very carefully consider how you would put a row "back together" if you ever needed to do such a thing. In fact, I'd recommend writing that code long before writing the audit code to support a "changes only" audit table just in case you change your mind.;-)
Jeff, are you saying it's best to save the entire row of data after an update rather than column(s) which have been saved?
I'm saying "It depends". If you save only the changes, they end up going into an EAV (Entity, Attribute, Value) table or at least it's little brother, an NVP (Name, Value Pair) table as you've suggested. That means that the "Value" column in both must be in some form of character format and it has to be wide enough to handle any and all values.
That being known, ask yourself...
1. How much of a load will the audit trigger that saves just the changes along with the required conversion put on the system especially on frequently updated tables?
2. How will I put the "changed only" data back together for any point in time? How long will it take to write and then use the code that does that?
3. How many additional columns of data do I need to capture such as datetime of the mod, who changed it, and from where? Then ask if you're really saving that much storage.
4. Am I going to save the original INSERT (usually a BIG mistake because it will more than double your disk space requirements)
If you have easy answers for the above, then by all means, use the "change only" method of auditing.
What I've found in practice is that it's, many times, just easier, faster, and more effective in the long run to do the following.
1. Don't audit the original insert. There's no need because it lives in the original table. I it hasn't been changed, nothing will appear in the audit log and that's a simple join if you want to combine the two.
2. Audit full rows because normally much more than 1 column is updated which eats up the supposed savings of a "change only" audit log.
3. Full row audits give you a super easy "point in time" audit trail. No fancy code required.
4. Full row audit triggers are much easier to write and will frequently be faster than the dynamics required to find and save only what changed.

Jeff,

Thanks very much for your comprehensive reply. You've certainly given me a lot to think on.

Mo