SQL Server Triggers containing Cursors (AKA: how *NOT* to audit)
I’m working with a client at the moment who has a lot of legacy code.
One of the things that makes the systems they’ve got so bad is that there’s little thought of performance when things have been written. An excellent example is the use of multiple nested cursors within triggers in SQL Server.
Obviously at some point a developer was given a requirement for auditting. As not all of of the tables (nor every column in a table) was required to be auditted they thought to themselves that triggers using cursors would be an excellent plan.
So, they created a table called “audit” that contained a timestamp; user id and name that caused the change, the source row id, table and column; and before and after values.
Now i’ve no doubt that this is a nice scheme for quickly showing a list of changes on a screen (i.e. select * from audit where sourcetable = ‘address’ and sourcetableid = ‘’ order by changedate DESC) but this is a performance killer if you ever have much concurrency and you update things in batch.
To get the data within the trigger to build the audit info you need to encode the table name, look up the username (for the user that made the change - as that was denormalised into this table too - ironically no doubt to speed up read performance), then iterate around another table that tells you which columns you’re interested in auditting, for each of those check if they’ve changed (in a cursor of course), then find the differences and do an insert for each.
So, if we’ve got 8 columns to change then we’ve got one trigger, one lookup for the user’s username, one cursor iterating around 8 “columns to audit” entries, at least 8 lookups against before and after data in the trigger, then up to 8 inserts into the audit table.
Now, some people are thinking… what’s the problem?
Firstly, one of volume: the client mentioned has a few million rows and does updates that can sometimes effect 10s of thousands of rows, suddenly a trigger per row hurts.
Secondly, they’ve obviously been looking at performance the wrong way - they have about 1000 users (and the table for users is the ntusername, vs the userid - not exactly large - so it’s easy for sql server to cache in tempdb as it’s used all the time). Next up the audit data isn’t going to change on a day by day basis - so why not generate that list of columns to check straight into the trigger code? At least that way you’ve skipped another table lookup.
I’m guessing you’re thinking - so what’s the right way? what’s the magic bullet/golden hammer? - There isn’t one, for every situation there’s a different solution, but it all starts by understanding the way your application needs to work.
I use two basic practises a lot;
a) Never update a row - have a timestamp against a row and the user id that created it and a marker that it’s historical. when you need to change data simply insert a new row (with the Now timestamp and the current user’s id) with the new data, then mark the original row as defunct. You either get the history live by showing before and afters, or have a batch job that turns it into user friendly stuff in a downtime (if one exists). If you use Stored Procedures/SPs (i’m assuming you do!) then it’s easy to implement this scheme in there.
b) Generate a table that matches the schema of the original table you wish to audit and prefix the name with audit or history (potentially you can trim down the columns in there if you’re not interested in all of them) and then insert the original data into the audit table whenever you go to update it. Again this is easy to do in the Stored Procs/SPs.
In general (a) is the simplest to implement but it’s only really usable if you have smallish data sets (especially if you put a clustered key on the “defunct” marker column - as that update will require a table reshuffle). (b) is more work but at least you’re segmenting your data into historical vs. current - so any stupid programmer mistakes (such as not using the defunct marker in a select query on the data can return a history row instead and confuse the user).
September 29th, 2008 at 11:12 pm
good article
October 2nd, 2008 at 1:44 pm
Thank you. What you need))
March 29th, 2010 at 11:48 am
comment…
Приятно читать блог…
March 30th, 2010 at 4:10 pm
cool story.
robe de soirée
April 3rd, 2010 at 5:31 am
Howdy, i read your blog often and i own an analogous one and i used to be simply wondering if you get a lot of spam comments? If thus how do you stop it, any plugin or anything you’ll be able to advise? I get thus much lately it’s driving me mad so any help is very abundant appreciated.
April 3rd, 2010 at 9:01 am
I admire the valuable information you offer in your articles. I will bookmark your blog and have my children check up here often. I am quite sure they will learn lots of new stuff here than anybody else!
April 3rd, 2010 at 4:16 pm
You made fantastic nice points here. I performed a search on the issue and discovered almost all peoples will agree with your blog.
April 4th, 2010 at 1:49 pm
You made tremendous great ideas here. I done a research on the subject and learnt most peoples will agree with your blog.
April 5th, 2010 at 6:36 am
awesome story.
robe noire
April 6th, 2010 at 5:06 pm
You had some good ideas there. I made a search on the issue and noticed most peoples will agree with your blog.
April 7th, 2010 at 5:13 am
Valuable thoughts and advices. I read your topic with great interest.
April 12th, 2010 at 12:21 pm
Aw, this was a really quality post. In theory I’d like to write like this too - taking time and real effort to make a good article… but what can I say… I procrastinate alot and never seem to get something done.
April 21st, 2010 at 12:41 pm
This is a really good read for me, Must admit that you are one of the best bloggers I ever saw.Thanks for posting this informative article.
May 2nd, 2010 at 5:25 am
I found your web page from bing and it is superb. Thankx for providing such an informative post!!!!
May 3rd, 2010 at 10:58 am
This a little bit funny. I found your site via search engine a few moment ago, and luckily, this is the only information I was looking for the last hours.
May 7th, 2010 at 9:56 am
fake watch
May 8th, 2010 at 3:01 am
replica watches
May 8th, 2010 at 4:51 am
replica watches
May 12th, 2010 at 3:39 am
You had great positive ideas there. I did a search on the issue and found nearly all peoples will agree with your blog.
May 12th, 2010 at 4:44 am
I liked it.
May 13th, 2010 at 3:56 am
Hi there, I found your blog on Google while seeking for first aid for a heart attack and your post looks very interesting for me.
May 13th, 2010 at 5:28 pm
replica watch
May 22nd, 2010 at 1:32 pm
fake watches
May 24th, 2010 at 8:58 am
rolex air king watches
May 26th, 2010 at 11:37 am
fake bvlgari
May 28th, 2010 at 4:05 am
Louis Vuitton Handbags
June 1st, 2010 at 2:07 am
replica handbags
June 4th, 2010 at 4:20 am
omega watches
June 5th, 2010 at 10:28 am
daytona watches
June 8th, 2010 at 2:00 am
bell ross
June 8th, 2010 at 2:40 am
fake breguet
June 8th, 2010 at 3:07 am
fake bell ross
June 8th, 2010 at 4:19 pm
breitling
June 9th, 2010 at 4:03 am
omega
June 9th, 2010 at 5:54 am
relica cartier watches
June 13th, 2010 at 8:59 pm
< a href=”http://avto-himki.ru/ yjeuaoy@auixech.ru” >1< / a >…
no more…
June 21st, 2010 at 1:42 am
replica watches
July 2nd, 2010 at 3:30 am
fake watches
July 2nd, 2010 at 3:50 am
watchvisa
July 2nd, 2010 at 3:52 am
fake watch
July 17th, 2010 at 11:31 am
fake cartier
July 19th, 2010 at 1:08 am
louis vuitton
July 23rd, 2010 at 1:48 am
replica watch
July 23rd, 2010 at 2:16 am
replica watches
July 28th, 2010 at 9:41 pm
replica watches
July 31st, 2010 at 6:45 am
rolex
August 4th, 2010 at 12:23 am
Thank you very much for that great article We’re down to triple digits for the first time since we’ve been together….
Interesting and informative. But will you write about this one more? Buy Cheap Electronic Gadgets Online…
August 5th, 2010 at 5:16 am
handbags sale
August 7th, 2010 at 8:59 am
You gave honest ideas there. I did a search on the issue and got most peoples will agree with your blog. It’s always a good idea to ask what people thought the book was about, and how this was conveyed.
August 9th, 2010 at 6:35 am
replica designer watches
August 10th, 2010 at 8:02 am
omega
August 12th, 2010 at 11:41 am
swiss watches store
August 13th, 2010 at 8:44 am
women’s watches
August 13th, 2010 at 1:50 pm
uggs
August 14th, 2010 at 10:50 am
ugg
August 16th, 2010 at 6:42 pm
swiss replica watches
August 17th, 2010 at 1:40 am
replica
August 18th, 2010 at 5:45 am
replica louis vuitton
August 19th, 2010 at 2:22 am
ugg boot
August 19th, 2010 at 2:18 pm
omega watches
August 19th, 2010 at 9:19 pm
small handbags
August 20th, 2010 at 8:28 am
watch on sale
August 22nd, 2010 at 8:32 am
ugg boots
August 24th, 2010 at 9:03 pm
lv
August 26th, 2010 at 8:08 pm
longines
August 28th, 2010 at 8:49 am
replica watches, replica watches
August 28th, 2010 at 9:39 am
replica watch, replica watch
August 29th, 2010 at 2:04 am
buckle watches
August 31st, 2010 at 6:54 am
patek philippe
September 6th, 2010 at 7:22 am
Fake Handbag
September 6th, 2010 at 9:32 am
ugg boot
September 6th, 2010 at 4:42 pm
ugg, ugg
September 6th, 2010 at 4:46 pm
replica watch, replica watch
September 7th, 2010 at 3:54 am
replica handbags,replica handbags