Difference between Stored Procedure and Trigger

By | November 1, 2018

Difference between Stored Procedure and Trigger

 

 

SQL Stored Procedure & SQL Triggers, both are the important objects of SQL. SQL stored procedure are the set of logically group of SQL statements which are a group to perform a specific task. Trigger are also similar to SQL Stored procedure as they can also be a set of logically group SQL Statement  but they are needed to perform some predefined action for automatic execution whenever users try to execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are defined on Tables and whenever any Insert /delete /update command executed on these tables, SQL statements defined in the SQL trigger got executed. This article will going to cover the difference between Stored Procedure and Trigger.

Stored Procedure Vs Trigger

  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.

 

 

SQL Stored Procedure and SQL Trigger are one of the important objects of SQL Server. This article tells about the difference between Stored Procedure and Trigger.

If you want to read more SQL Concepts, you can find the list of all SQL articles in page SQL Tutorial

If you want to read more Azure SQL Concepts, you can find the list of all Azure SQL articles in page Azure Tutorial

Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com.

Read More

Sql Server – How to write a Stored procedure in Sql server

SQL Triggers – An Introduction

SQL Script to find all the triggers defined on a database or on a single table

Advantages of SQL Stored Procedure

SQL Trigger – Advantages & Disadvantages

 

 

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.

2 thoughts on “Difference between Stored Procedure and Trigger

Leave a Reply