Using Triggers for Business Rules

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

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.

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