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
- 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.
- 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.
- Stored procedures can be scheduled through a job to execute on a predefined time, but we can’t schedule a trigger.
- Stored procedure can take the input parameters, but we can’t pass the parameters as an input to a trigger.
- Stored procedures can return values but a trigger cannot return a value.
- We can use the Print commands inside the stored procedure to debug purpose but we can’t use the print command inside a trigger.
- 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.
- 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.
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 email@example.com.