Post reply

Good practice or not?

  • February 7, 2006 at 10:01 am

    #90537

    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

    #619347

    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

    #619355

    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.

    LinkedIn Profile

  • February 8, 2006 at 1:40 am

    #619449

    Thanks for the feedback, both of you. Much appreciated.

    Windows 2008 Server | SQL Server 2008

  • February 8, 2006 at 10:27 am

    #619573

    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