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
Vijaya Kumar Gulappa
Vijaya Kumar Gulappa
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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
Revi Nadesan
Revi Nadesan
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 19

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.

 


Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1415

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.





noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048

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
avi_a
avi_a
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
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

 





rajesh-132317
rajesh-132317
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
its good information. thanks!!
RonKyle
RonKyle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 3337

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.





colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2723 Visits: 715

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/
Ondrej Pilar
Ondrej Pilar
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

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


Thomas-282729
Thomas-282729
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 482
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.


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