what is main difference between trigger and procedure

Hi Sharad,

1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.

2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.

3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

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

5) Stored procedures can return values but a trigger cannot return a value.

6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.

7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.

8) 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.

Please refer the below link

http://www.c-sharpcorner.com/blogs/7852/difference-between-the-stored-procedures-and-trigger.aspx

http://beyondrelational.com/modules/17/interview-questions/238/interview-questions/7056/difference-between-stored-procedure-and-trigger-in-sql-server.aspx

Thanks

2 Replies

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

Satyapriya Nayak14y ago
Hi Sharad,

1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.
2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.
3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

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

5) Stored procedures can return values but a trigger cannot return a value.

6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.
7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.
8) 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.

Please refer the below link
http://www.c-sharpcorner.com/blogs/7852/difference-between-the-stored-procedures-and-trigger.aspx
http://beyondrelational.com/modules/17/interview-questions/238/interview-questions/7056/difference-between-stored-procedure-and-trigger-in-sql-server.aspx

Thanks
14y ago
Triggers are called when an insert / update happend in the sql table.
Stored procedures are set of sql statements , that are used to perform select,insert,delete or update operations.