Trigger Vs Output Clause

Question

Post reply

Trigger Vs Output Clause

nagarajanmm-699934

SSC-Addicted

Points: 421
More actions
May 27, 2008 at 1:38 am

#380678

Hi,

Apologize if this question has been raised already.

We are working on a highly transactional ticketing system, in which we are expecting around 25,000 - 30,000 tickets/ day. We need to audit trail the critical tables. The schema of the audit tables are similar to the critical tables with the additional Audit_ID (Identity Column), Plus Action type (Insert/ Update).

Queries:

i. In the above scenario we are uncertain about choosing Trigger Vs Output Clause, in terms of Performance.

ii. Auditing should be done for Insert/ Update/ Delete operation. In all the cases, I will enter the entire record (Inserted/ Deleted) to the Audit Table with the proper Action information (I, U, D).

iii. I am trying to explore OStress tool for Performance Analysis, but finding it hard since did not get enough documentation.

If someone can have already done this exercise, kindly suggest me on the above.

Thanks in advance.

Nags

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

My general preference for the situation you describe is to use the trigger. I prefer this mainly because this bonds the auditing to the table rather than rely on a person writing the SQL to remember to include the auditing code. Also, this will likely greatly reduce the number of places where this code needs to be maintained.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

As far as performance, I do not think you will see a significant difference if both are written correctly. I would recommend you test both ways.

I also agree that the maintenance would tend to be easier using triggers. I tend to use them for auditing and have had very few issues with them.

Also, triggers can be disabled and enabled on demand. This may not seem like an issue right now, but if you need to disable auditing for some big updates at some point, this can be of great benefit. It sounds counter-intuitive to disable your auditing, but I have found this handy at times.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Writing twice is writing twice. I've found the OUTPUT clause to be a bit more flexible in some ways than a trigger and it has the added value of being directly in your face. However, you have to know to put it into each INSERT/UPDATE/DELETE statement where as a trigger will be more likely to prevent coding errors. I'd still go with the OUTPUT clause, but you need to have good coding discipline in place.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

I agree with Kent and Michael: Auditing is usually considered to be an operational feature, not a functional one, and thus I would prefer to implement it using Operational tools, like a trigger.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

nagarajanmm-699934 SSC-Addicted Points: 421 More actions · Answer 5

nagarajanmm-699934

SSC-Addicted

Points: 421

May 27, 2008 at 10:25 am

#820669

Thanks for your replies. I understand Trigger has more benefits than the Output Clause. Only thing I am worried about is the Performance of the Trigger. Is there any performance comparision on both available?

Thanks,

Nag

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

nagarajanmm (5/27/2008)

Thanks for your replies. I understand Trigger has more benefits than the Output Clause. Only thing I am worried about is the Performance of the Trigger. Is there any performance comparision on both available?

Thanks,

Nag

considering you have roughly the same abilities in both cases - I am not sure that there's a compelling reason why either would be intrinsically faster or slower. It comes down to how you're writing them.

It can depend on the situation as well. For example, it may be that the audited activities are so very infrequent compared to the overall activity that you might not care to have a checking function fire each and every time. For example - I had to write an audit process to capture records as they were when being passed from one person to another: a given person could issue as many updates as they wish to get their step done, but we needed to snapshot it as it looked last when it got handed off to the next higher technician ("escalation snapshot"). In that case - it was better to write it as an OUTPUT clause, since the escalations might happen one in several thousand update operations. However, most auditing processes require ironclad consistency, in which case having the trigger is the way to go.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

nagarajanmm-699934 SSC-Addicted Points: 421 More actions · Answer 7

nagarajanmm-699934

SSC-Addicted

Points: 421

May 27, 2008 at 10:52 pm

#820964

Thanks Matt. It was clear on when to choose Output Clause over Trigger.

Nags