Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Stored Procedure vs Triggers Expand / Collapse
Author
Message
Posted Monday, April 2, 2007 3:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 4, 2007 7:58 AM
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.



Post #355477
Posted Tuesday, April 3, 2007 11:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 16, 2011 3:56 AM
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?
 
Post #355697
Posted Thursday, April 5, 2007 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 5,303, Visits: 9,677

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

Post #356253
Posted Thursday, February 14, 2008 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2008 7:02 AM
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.
Post #455672
Posted Thursday, February 14, 2008 7:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2008 7:02 AM
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.
Post #455674
Posted Thursday, December 9, 2010 5:04 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 12:17 AM
Points: 16, Visits: 128
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
Post #1032375
Posted Thursday, December 9, 2010 5:56 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 8,677, Visits: 9,203
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
Post #1032396
Posted Wednesday, February 16, 2011 3:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 5:03 AM
Points: 3, Visits: 34
Can someone give examples of scenarios when to use a trigger and when to use a stored procedure.
Post #1064808
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse