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 Saturday, July 24, 2004 5:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 8:31 AM
Points: 56, Visits: 87
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gvijayakumar/storedprocedurevstriggers.asp.



Blog: Gulappa
Post #128054
Posted Monday, August 30, 2004 8:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 1:44 PM
Points: 277, Visits: 16

This article is practical if you are only interested performance. In most application(s), when you implement audit trails, you would capture the following:

1) Why the data was modified

2) When the data was modified

3) Who modified the data.

If somebody has access to data via Enterprise Manager, he/she will able be to manipulate the data.This action  will not provide any tracebility or audit trail. If you dealing with very sensitive data, then you have to capture any kind of action related to that data based on the business rules regardless of the tools (QA - Query Analyzer, EM - Enterprise Manager or via Application).

Hence, I doubt the method you suggested/proposed is practical.

Thanks.

 

Post #134464
Posted Monday, August 30, 2004 1:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124

The trigger method seems to me to be the most reliable method for capturing audit data.  I have used "INSTEAD OF" triggers to maintain "last modified by" fields to prevent users from overriding the value even with Enterprise Manager (unless they have knowlege & permissions to disable the trigger).

The stored-procedure method works as long as you make sure developers can't or won't write anything that updates the table except through the stored procedure.

The article shows some good examples of evaluating performance by examining the syscacheobjects table.  However, none of this examination appeared to apply to the topic.  You called either a trigger, a stored procedure, or both, four times.  Am I missing something?

Why would your method III use an insert-select for the audit table instead of the original parameters, like this?

Insert Into DummyTable1_Audit Values (@EmpID, Replicate (@EmpName,20), 'N')

Another gripe would be your use of "SELECT *" and INSERT with no field list.  Any change to the structure of the table would kill both your trigger and stored procedure.  This has nothing to do with trigger vs. stored procedures, but example code that might be seen by fledgling DBAs should not teach them bad habits.




Post #134519
Posted Monday, August 30, 2004 6:17 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027

There are A LOT of things left out here:

1. If your stored procedures are supposed to carry out the audit trail capabilities you will be MIXING Audit Logic with Business like logic with in my opinion is a BAD thing, complicates the procedures and Makes it Harder to further modify.

2. A trigger would fire ONCE per STATEMENT with that in mind if you need to load data of several records at a time stored procedures will be a bad idea from the performance stand point if you compare it with statements like:

INSERT TBL(...)

SELECT fld1,fld2,...

UNION ALL SELECT fld1,fld2,...

UNION ALL SELECT ....

3. In Triggers is a lot easier to tune to the field level change in case you decide that audit should be performed only if certain column(s) change due to the built in UPDATED() facility

4. Table structure does not necessarily has to be with an Identical Mirrored Table you could use

Create Table ( id int , TranasctionType char(1), tableName varchar(10), Pkey varchar(10), FieldName varchar(40), Oldvalue varchar(200), NewValue varchar(200)  Simplifying A LOT the Storage and Flexibility of the audited Solution. Try that with Procedures mixing the logic and getting fairly good results 

5. IF any of the Tables Has the CASCADE DELETE ON, the sp will be BLIND to child tables modification or if you try to compensate for that it will become increasingly difficult to code as the hierarchy gets deeper,  which won't happen in the case of the Triggers

6. Scriptability of Audit logic can be achieved by both but I have found fairly common that some procedures are usually tuned independently and that is another reason to keep audit logic separated

All in all the explanation of syscaheobject was well presented and it could be a nice starting point to those who want to learn about performance. The intention of my explanation here is just to demonstrate that there is NOT a clear cut to one approach or the other as the article seems to point out and to shed some light on things that were NOT even mentioned in favor of the chosen solution.

HTH

 




* Noel
Post #134575
Posted Sunday, September 05, 2004 1:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 7:19 AM
Points: 23, Visits: 36

As for the article, I was expecting to see some insight on runtime performance using triggers vs SPs. For instance, from my own experience I found that using INSERTED from triggers takes a lot of time, and I assume that using SPs for the audit work would be better on performance. I've never had the time to do extensive research on this subject, and I guess I was expecting to see some in this article. pitty..

As far as the recommendations go, I must agree with noeld:

  1. From a programmer's perspective it seems to me that Part II would be better for maintenance in the long run (easier to mintain two simple objects than one compilated one).
  2. In the long run, even if we make sure to use SPs, YOU ALWAYS END UP WITH MORE THAN ONE SP PER TABLE ACTION! Fully-generic SPs never withstand the performance barrier, and you always end up making special SPs for special cases , thus having to maintain the audit logic in each SP turnes out to be a hassle and is prone to mistakes.

Either way, I was glad to see the article and it was interesting to read - learned alot about caching etc., which is always good to know.

 - Avi

 




Post #135414
Posted Monday, September 06, 2004 2:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 04, 2005 10:40 PM
Points: 9, Visits: 1
its good information. thanks!!
Post #135464
Posted Friday, March 31, 2006 6:56 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:20 PM
Points: 788, Visits: 1,917

Triggers serve best as methods to maintain low level data integrity that cannot be done through standard relational methods.  The inactivation of a major site, for example, should via trigger cascade the inactiviation of all the subsites.  Audit records are some form of this low level integrity.  The changes need to cascade into some form of permanent record of the change. 

Stored procedures perform data actions.  The can store business logic but ideally this is performed in a middle tier.  They should not normally be used in the same way a trigger does.

We would have never had the debate.  Unless someone could show that there were significant performance reasons to do otherwise, the debate that was held as the beginning of the article would not have gone on long: we audit via trigger. 

That it's also a good idea for all the reasons the previous posters have mentioned is just gravy on the overall conclusion.




Post #270122
Posted Friday, March 31, 2006 7:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695

If I remember correctly doesn't an update or insert trigger force all data updates to be deferred updates rather than in place updates, thus contributing to page splits and fragmentation?

I've also always found triggers slow an oltp application badly - well it did last time I saw it implemented ( briefly - it had to be removed to return the application to usability ).

Have to be honest I avoid triggers unless there is absolutely no alternative.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #270124
Posted Friday, March 31, 2006 7:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 03, 2007 1:56 PM
Points: 1, Visits: 1

This reply seems to be MUCH more valuable than the original topic...

Post #270125
Posted Friday, March 31, 2006 8:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 14, 2014 9:12 AM
Points: 89, Visits: 478
Something that has not been mentioned in all of this is the method by which applications are connecting to the SQL Server. If they are connecting through direct, trusted connections, then the trigger will have access to the username making the DML statement and auditing via the trigger will work fine. However, if applications connect through an abstraction layer such as COM+ running under its own user or a website, then the trigger will only have access to that middle-tier username (IUSR..., your COM+ user etc.) and it will make the audit data meaningless. In this last scenario, you have to pass the username from the presentation tier down to the database and in that scenario stored procedures make more sense.

I generally try to avoid triggers when I can. The ugliest part of triggers is that they fire for everyone all the time which can be a pain in administrative situations. If auditing is really the driving force and if you have access to the end user connecting to SQL (which presumably you would if triggers were an option) then I would recommend finding a program that monitors the DB log directly. That eliminates the use of triggers and provides your audit trail.

Post #270175
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse