February 7, 2006 at 10:01 am
Hi
I'm torn about what to do here.
Certain tables in my db have triggers. The triggers fire short events. All the code needed to execute the event is in the trigger.
Would it be a good idea to move that code from the trigger into a stored procedure and call the stored procedure from within the trigger? (passing any necessary parameters to the sp of course).
I've read that the trigger is 'pre-compiled' in a similar fashion to a stored procedure so it might make no difference.
What are your thoughts?
Windows 2008 Server | SQL Server 2008
February 7, 2006 at 10:07 am
It is very hard to convert a well written trigger to use a stored procedure.
Most likely your trigger will not work on multiple row update very well.
Edit: Even if you can use a stored procedure I would not.
Tim S
February 7, 2006 at 11:05 am
Are you experiencing problems with the way you are doing things at present?
If not then I would take the view that if it isn't broke don't fix it.
I try and restrict my use of triggers to situations where there is no alternative due to the performance hit.
I have the luxury of having all my data access via stored procedures therefore the logic for the trigger can be incorporated into those procedures.
If I allowed direct table access I would not have that luxury.
I have seen a post where the trigger does
SELECT * INTO #inserted FROM inserted
in order to get around the performance hit. This also gets around the issue of writing for multiple record insertions.
February 8, 2006 at 1:40 am
Thanks for the feedback, both of you. Much appreciated.
Windows 2008 Server | SQL Server 2008
February 8, 2006 at 10:27 am
Something else to consider would be whether you want the triggers to fire regardless of what originates the query. For example, if you just update a table in QA, you can bypass whatever logic the stored proc would normally fire from within your app. If you don't need them to fire each time and insert/update/delete happens, then you're probably okay moving them if you need to.
-Pete
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply