Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure vs Triggers


Stored Procedure vs Triggers

Author
Message
ian scott-fleming
ian scott-fleming
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
I see four issues raised in the article and the following discussion:


1. Efficiency: author seems to show that there is no difference (or at worst) small difference in efficiency between using triggers and stored procedures for logging transactions to the audit trail table(s), but perhaps I missed something on that.

2. Security: some people are very concerned about someone else maliciously manipulating their database (banking, eg.). The audit trail task should be completely independent from the insert/edit/delete, and be automatic. In that case triggers seem to be the safest way to have a solid audit trail.

3. Clarity: triggers are hidden from the readers of the code – unless the readers of the code know about the trigger’s existence when they look at the insert/edit/delete procedures, they would not be able to understand how the code works.

4. Maintainability: if multiple insert/edit/delete routines exist and triggers are not used, then each routine must include identical audit-trail code. Changing the audit-trail task requires going back and changing all versions, or else the system gets broken.

For those who do not need to worry about 1 or 2 (like me), then clarity and maintainability are the core issues. The time-tested solution to this is modularity: extract the audit-trail code to its own stored procedure, and include a call to the audit-trail procedure in every insert/edit/delete procedure. This way changes to the audit-trail task occurs in one place only, and changing it shouldn't affect the other code. Similarly, if the insert/edit/delete code is modular, then changes to it need to occur in one place only, even if you have several specialized routines. The cost is efficiency -- the extra overhead of a second procedure call each time -- but if this is not a big concern then the benefit of clarity will outweigh the cost.
Kay Thong
Kay Thong
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
I can't say much about the technical content of this article because I just could not bring myself to read beyond the first couple of paragraphs - the English was that atrocious!
I guess there is something to be said about the virtues of content over style, but when the reader has to fathom a guess at what the author is trying to convey every third word or so, then the task becomes an uphill struggle.
I know that there is bound to be thoughts (if not yells) of "pedant" out there, but surely I am not the only voice for the correct use of English and clarity in the written word! Or does technical expertise transcend language barriers? Maybe there is a market for proof readers and ghost writers in IT?

John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159

I applaud the author's efforts to write in a language that isn't his mother tongue. It's come out far more clearly than I would have managed had I been attempting to write in French or Spanish, for example. I agree about the need for the correct use of English and clarity in the written word, but I think we should save our ire for those who should know better!

John


jeromed
jeromed
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Its best to avoid triggers at all costs. We've been having problems with them, especially when related tables each have triggers. Besides performance issues, the triggers weren't written properly. By the way several people have worked on the database. Data would be validated at the presentation layer. Upon saving information the triggers would fire and that would cascade to many tables. At the end you have information that's mixed up. And you don't know where to start debugging.
jeromed
jeromed
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Its best to avoid triggers at all costs. We've been having problems with them, especially when related tables each have triggers. Besides performance issues, the triggers weren't written properly. By the way several people have worked on the database. Data would be validated at the presentation layer. Upon saving information the triggers would fire and that would cascade to many tables. At the end you have information that's mixed up. And you don't know where to start debugging. You just have to be trigger-happy if you want to use triggers. Stay away from them.
dhaneenja-755935
dhaneenja-755935
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 133
Hi Vijaya Kumar,
recently i have read your article and it's helpful.And I have small doubt why are you used this,
(This works for Oracle but not MSSQL)
"I have used “Truncate” statement instead of “Delete” because I do not want to rollback my data."

when you Truncating records...B'cos Truncate also can be ROLLBACK...

Thanks
Tharindu Dhaneenja

Tharindu Dhaneenja.
MCTS,MCITP(SQL Server),OCA(Oracle)
http://www.databaseusergroup.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10767 Visits: 12019
Pedrsonally I hate triggers, and avoid them where possible. For audit it is not possible, triggers are essential - how can I have reliable audit data using some method that doesn't automatically record the things that need auditing, some method that relies on the user whose actions I want to audit to call some audit procedure?

Tom

Amol Verma
Amol Verma
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 34
Can someone give examples of scenarios when to use a trigger and when to use a stored procedure.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search