SQL – Stored Procedure Vs Triggers

A Stored Procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

“Stored procedures are stored in a pre-complied form. That is once a stored procedure is executed, the compiled code is used in subsequent calls. You can test your stored procedures easily due to less coupled”

Triggers are extremely powerful and useful feature of SQL because they are completely Global and Stealthy They are good and necessary for data integrity as well as complete the DBMS-specific activity under the control of the database where it belongs. They are basically used for audit logging.

 “Triggers are stored programs, which are automatically executed or fired when some events occur on the table like insertion, deletion or updation of data. It is a database object which could be defined on the table, view, schema, or database with which the event is associated”.

Difference between Trigger and Stored Procedures

Contents	Triggers	Stored Procedures
Implementation	Triggers can only be implemented on tables or views. A trigger cannot be written within a stored procedure.	Stored Procedure in independent code that can be specific to database. A stored procedure can be written within a trigger
Event and action	Triggers are event and action based to perform some specific tasks and they are free to run automatically whenever the event is occurred on the table on which the trigger is defined	They are not based on the event and action. They are not able to run automatically and depend on the other manual process.
Execution Mode	They execute implicitly.	A stored procedure must be called explicitly.
Schedule Mode	This is not possible for triggers because they are event and action based.	Stored procedures can be easily scheduled through a job to execute on a predefined time.
Transactions	Transactions are not allowed in Triggers.	Transaction statements like begin transaction, commit transaction, and rollback can be used easily inside a stored procedure.
Parameters	This feature is not applicable in triggers.	Stored Procedures may or may not have one or more input/ output parameters to perform some tasks.
Front end applications	This feature is not applicable for triggers.	Stored Procedures can be easily called from the front end (.asp files, .aspx files, .ascx files, .rdl etc.)
Return Value specific	A trigger cannot return a value.	Stored procedures can return values.
Nesting	Directly you cannot call another trigger within a trigger.	A stored procedure can be called from inside another stored procedure.
Task Specific	The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.	Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks.

Learn more on another features of SQL as:

1. SQL - Stored Procedures
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL - Stored Procedures Vs Ad-Hoc (In-Line) Queries
8. SQL - Stored Procedures Vs Functions
9. SQL - Stored Procedures Vs Triggers
10. SQL - Merge Statement
11. SQL - Functions
12. SQL - Triggers
13. SQL - Indexes
14. SQL - Cursors