SQL Server Trigger options

Basil Howitson

10y1k

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Basil Howitson10y ago
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.
Dharmesh Singh10y ago
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.