Difference between Triggers and Stored procedures

 Posted by Ddd on 2/1/2011 | Category: Sql Server Interview questions | Views: 46968 | Points: 40

Both are database objects containing blocks lof code that can be used for implementing business logic

The differences are:

1)Triggers fire automatically but they need events for that.
(Example: create,alter,drop,insert,delete,update)
Procedures have to be explicitly called and then executed.
They do not need create,alter,drop,insert,delete,update.
we can also execute procedures automatically using the sp_procoption.

2))we cannot pass parameters inside the triggers,
but we can pass parameters inside stored procedures
example: if we want to display a message "error"

using a trigger: we need some DDL/DML Statement
using a procedure: NO DDL/DML is needed

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Muhsinathk on: 9/13/2012 | Points: 10
Differences between storedprocedures and triggers
1. When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3. Within a trigger you can call specific s.p but within a sp you cannot call atrigger

4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.

5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.

6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

Login to post response