March 18, 2009 at 12:21 pm
Hello, I am in the process of designing an audit process for a new application. For example we would like to audit and keep full history of customer information. Say a customer changes their address. I would have a trigger on the customer table that would pull back all the necessary customer data and compare the old to the new. If there is a change, that info would be recorded in a sperate audit table. there will be a BI application used to access these tables. Has anyone done anything similar to this where you would be able to provide some suggestions on design and possible a trigger example? Thank you in advance for your help.
Best Regards,
~David
March 18, 2009 at 1:07 pm
These two articles and their discussions should get you well started on that:
http://qa.sqlservercentral.com/articles/Auditing/63247/
http://qa.sqlservercentral.com/articles/Auditing/63248/
- 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 19, 2009 at 11:53 am
This was very helpful, especially the second article. Thank you kindly.
Best Regards,
~David
March 19, 2009 at 12:50 pm
You're welcome.
- 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 20, 2009 at 9:15 am
It looks like it is recommended that a clustered ascending sort primary key be placed on the audit/log tables. Why is that better for performance? Thank you.
Best Regards,
~David
March 20, 2009 at 9:18 am
Because that way each insert is at the end of the table, and the table doesn't have to be split in the middle repeatedly because of inserts. Table splits take time, and lots of them can slow things down quite a bit.
- 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 20, 2009 at 9:20 am
Excellent. Thank you!
Best Regards,
~David
March 20, 2009 at 9:30 am
How are rows inserted when a table has no indexes? Is the effect the same? Thank you.
Best Regards,
~David
March 20, 2009 at 1:54 pm
Hello, in a recent design meeting it surfaced that these triggers would need to compare the new values in the triggering table with values in another table before proceeding down a given logic path. Is this something that could be a potential performance bottleneck? Is this common practice? Thank you.
Best Regards,
~David
March 20, 2009 at 2:03 pm
David Kranes (3/20/2009)
How are rows inserted when a table has no indexes? Is the effect the same? Thank you.
It should just have the clustered index.
If you want more indexes, move the data over to another table periodically, and index that.
The reason for this is that inserts to tables with indexes have to insert into the table, and into the index. That's more time. Not much, but it can add up on a busy server.
Inserts into tables with no indexes, even no clustered index, work the same as indexes into tables with indexes. The difference is that the indexes also have to be modified, which means more work, which means (slightly) more time.
- 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 20, 2009 at 2:05 pm
David Kranes (3/20/2009)
Hello, in a recent design meeting it surfaced that these triggers would need to compare the new values in the triggering table with values in another table before proceeding down a given logic path. Is this something that could be a potential performance bottleneck? Is this common practice? Thank you.
I'm not sure if that's a continuation of the subject of audit logging. If it is, can you clarify what you mean and what effect it would have on the audit log? If it's a separate question, please start a new thread with it, and explain a bit about what you'd be doing with the data and why. In either case, I need a bit more data to answer you meaningfully.
Simple answer, more work = slower performance. Long answer = I need to know more about what you are doing in order to answer.
- 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 20, 2009 at 2:16 pm
I believe it is part of this thread but forgive me if it is not and I can start a new one. The trigger I am writing needs to include logic which will compare key field values in the inserted table with values in 2 or more tables in order to determine if and what needs to be inserted into the triggering table. Basically I need to pull back values from other tables based on key values in the inserted table and compare them to determine the next step. Is this something that is commonly done or do triggers generally stay within the underlying table? Is this bad practice? Thank you.
Best Regards,
~David
March 20, 2009 at 2:23 pm
It's a common enough practice. I try to avoid it by having that kind of logic in the insert proc, instead of in a trigger, but that's mainly for maintenance and documentation purposes, not performance issues.
The performance of such a trigger will depend a lot on how it's written, and how well designed the tables involved are. Of course, it can also be affected by the amount of data in the tables, though absolutely huge tables can have fast performance if they are really well designed and maintained.
- 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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply