SQL Server database hangs on trigger execution

Question

We have implemented 6-7 trigger on a table and there are 4 update trigger out of these. All of the 4 triggers require long processing because of data manipulation and conditions. But whenever trigger executes then all the pages on the website stop responding and hangs for every other user from different systems also. Even when we execute update statement in SQL Server Management Studio on the trigger holding table then it also hangs. Can we resolve this hanging issue by shifting this trigger code into the stored procedure and call this stored procedures after update statement of the table?

Because I think trigger block the table access to the other user at the time of execution. If not then can anyone provide the solution over it.

Answer 1

Without actually seeing the triggers it's impossible to diagnose this confidently but here goes...

The TRIGGER won't set up a lock as such but if it sets off other UPDATE statements they'll require locks and if those UPDATE statements fire other triggers then you could have a chain reaction that produces the kind of grief you seem to be experiencing.

If that sounds like what might be happening then removing the triggers and doing the processing explicitly by running a stored procedure at the end may fix it. If the stored procedure is rubbish then you'll still have problems but at least they'll be easier to fix. Try to ensure that the Stored Procedure only updates the records that need updated

The main problem with shifting the functionality to a stored procedure that you run after the update is ensuring that it is in fact run every time.

If your asp.net skills are stronger than your T-SQL skills then this should be a far easier problem to solve than untangling a web of SQL triggers.

The other issue is that the between the update completing and the Stored Procedure completing the records will be in an intermediate state showing the initial change but not the remaining ones. This may or may not be a problem in your case

Answer 2

Can we resolve this hanging issue by shifting this trigger code into the stored procedure and call this stored procedures after update statement of the table?

You have a box that weights a ton. Does it get lighter when you put it into some nice packaging?

A trigger is already compiled. Putting it into a stored procedure is just dressing it up differently.

Your problem is that you abuse triggers to do heavy processing - something they should not do by design. Change the design.

Because I think trigger block the table access to the other user at the time of execution.

Well, triggers do NO SUCH THING - so you think wrong.

A trigger does what it is told to do and an empty trigger sets zero locks (the locks are there from whatever triggers it). If you do set up a table wide lock - fire whoever did that and redesign.

Triggers should be fast, light and be over fast. NO heavy processing in them.

Answer 3

Triggers are dangerous - they get fired whenever things happen, and you have no control over when and how often they fire.

You should definitely NOT do any time-consuming processing in a trigger! A trigger should be super fast, and lean.

If you need processing - let the trigger record the info needed into a separate "command" table, and have another process (e.g. a scheduled SQL Agent job) that checks that table for commands to be executed, and then executes those commands - separately, independently of the main application, in a separate execution path.

Don't block your main app by doing excessive data processing / manipulation in a trigger! That's the wrong place to do this!