I am in the process of giving one of the company's web apps a revamp. I will use ASP.Net MVC for the user interface and MS SQL Server for the back-end. I busy with the optimization of a database, which includes its stored procedures and triggers. What is the best practice with reference to the use of triggers and stored procedures? Should most or all of the T-SQL be in stored procedures and the execution of the stored procedure triggered by the triggers; or should the T-SQL be in the triggers? How is the speed of T-SQL affected through the use of stored procedures in triggers? I appreciate any advice I can get.

Thank you for the advice Dharmesh. I have limited my trigger to execute only against the record with a PrimaryID thathas been inserted or updated.Should I split my triggers? For example, one trigger to target inserts and another trigger to target updates.

I think Procedure is best because it execute when you call it. Trigger hit database every time when record delete or insert or update it depent on which type of trigger created.

Answers

SQL Server Trigger options

Basil Howitson

816

I am in the process of giving one of the company's web apps a revamp. I will use ASP.Net MVC for the user interface and MS SQL Server for the back-end.

I busy with the optimization of a database, which includes its stored procedures and triggers.

What is the best practice with reference to the use of triggers and stored procedures?
Should most or all of the T-SQL be in stored procedures and the execution of the stored procedure triggered by the triggers; or should the T-SQL be in the triggers?
How is the speed of T-SQL affected through the use of stored procedures in triggers?

I appreciate any advice I can get.

Answers (2)

Next Recommended Forum

The stored procedure or function does not have a return type

Conversion of nvarchar to bigint