SQL Server Performance

Using Triggers for Business Rules

Discussion in 'Performance Tuning for DBAs' started by rsell1, Mar 14, 2005.

  1. rsell1 New Member

    I have recently taken over support on a SQL 2000 DB that contains a table with 175 columns and 42 tiggers. The application lacks a "Rules Engine" therefore the staff created trigger after trigger to enforce business rules. We are experiencing an emormous amount of locking (imagine that...).

    Is it better (performance and lock prevention wise) to consilidate the business rules by DML action? For example all triggers testing for UPDATE(column1) be consolidated to one trigger, testing for UPDATE(column2) be consilidated into another trigger etc? Or should all the update triggers be consolidated into one UPDATE tigger, all insert triggers be consolidated into one INSERT trigger, etc.

    Thanks for any assistance.

    Bob
  2. Adriaan New Member

    I often find that a lot of items in triggers are duplicated between INSERT and UPDATE, so you can probably create a combined trigger in those cases. I wouldn't necessarily split out by specific columns, as all triggers will fire regardless of any specific columns. If you end up with a very big trigger definition, make sure to build code with a good flow.

    One thing I always check for in a combined trigger is if a record exists in the inserted virtual table (store 0/1 in a bit variable) and the same for the deleted virtual table. This will tell me if the trigger fires because of a cascading update or delete (both bit variables are 0), if the event was a deletion (bit for inserted: 0, bit for deleted: 1) or an update (both variables are 1). This can help you simplify the flow of the procedure.

    And finally, I make sure to use a lot of EXISTS clauses, and that queries center on the deleted and inserted virtual tables whenever possible, to reduce the number of rows queried.
  3. satya Moderator

    As most of it covered by Adrian, I suggest following links for better understanding about triggers:
    http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/default.aspx
    http://www.devx.com/getHelpOn/10MinuteSolution/20545
    http://www.sqlservercentral.com/columnists/bkelley/triggers_1.asp
    http://www.sqlservercentral.com/columnists/bkelley/triggerswhatsnew.asp


    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. rsell1 New Member

    Thanks for your assistance. It appears that I have quite a bit of work to do.

Share This Page