triggers and SP

Question

which one is faster triggers or stored procedure? and why?

Manas Mohapatra · Answer

Triggers and Stored Procedure are objects in Database. They have different different purpose to use in database. StoredProcedure: It is a database object which helps to execute batch of SQL statements. Suppose you have a requirement to insert some records in table and same time you also need to update some records in other table. Means you need to execute multiple statements here - SP plays vital role here. Let's discuss another scenario there is a requirement to fetch all information about project report and information stored in multiple tables. So you can write a SP and implement your logic to fetch information regarding project. It also send multiple result sets. Trigger : It is a database object which executes when any DML(insert update delete) operation occurs in tables. It is mostly use in Audit purpose. Suppose there is a table called EmployeeSalary table. Now you want to update some employee's salary. And later also you want salary history of an employee. So that scenario trigger will help you to implement the same. You need to create trigger on table EmployeeSalary table on insert/update of record and inside trigger you need to write logic to fetch previous salary current salary. Then you need to insert in another table for employee salary history. In trigger it uses to tables called Inserted and Delete. These table are called as Magic table in SqlServer. So both has different purpose in Database. So you need to choose as per your requirement. Note: Don't compare which is faster.