Sunday, July 27, 2014

Difference between Trigger and Stored Procedure

Both, stored procedure and trigger are important object of database. So, in this article, I'll explain the subtle differences between stored procedure and trigger. Before, going into the differences, first lets see what is stored procedure and what is trigger.

What is trigger in database

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database. DML triggers execute when a user tries to modify data in a database table. A trigger can be executed on INSERT, UPDATE, or DELETE statements on a table or view. Triggers that are fired before any action performed are called Before Triggers and trigger that are fired after the action has been performed are called After Triggers.

Types of Triggers

DDL Triggers - This type of trigger are fired against DDL statements like Drop Table, Create Table, Or Alter Table. DDL Triggers are always AFTER triggers.
DML Triggers - This type of triggers are fired against DML statements like INSERT, UPDATE, DELETE. DML Trigger can be Instead Of, Before and After. Instead of trigger are fired in place of the triggering action such as an insert,update, or delete.

Below is the example of a simple trigger:


CREATE OR REPLACE TRIGGER INCREMENT_SALARY
BEFORE DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
   UPDATE CUSTOMERS SET SALARY = NEW.SALARY;
END

What is stored procedure in database

A stored procedure is a set of SQL statements with an assigned name that's stored in the database compiled into a single execution plan. It may or may not return result. Mainly, those SQL statements and logic that need to perform a commonly performed task can be designed and coded in a stored procedure.

Below is the example of a simple stored procedure:


CREATE PROCEDURE USP_GET_ADDRESS 
AS
BEGIN
     SELECT * FROM AdventureWorks.Person.Address
END

Below are the points that differentiate a stored procedure from a trigger:

Stored Procedure	Trigger
The stored procedures is normally used to performing user specified tasks. It can have the parameters and return multiple results set.	The Triggers is normally used for auditing work. It can be used to trace the activities of the table events.
The procedures can have the input and output parameters. The datatypes can be built-in datatypes or user defined data types.	The Triggers cannot have any parameters.
The stored procedures can be run independently. It stores as a database object. It can be called from an external application.	The triggers executes based on table events. The DML triggers are get executed based on the table events defined on the particular table.
The stored procedures cannot call the triggers directly. But when we do the DML operations on the table within in a stored procedure, and if the corresponding table has trigger then trigger will be executed if the specified event associated with trigger is executed.	The triggers can call stored procedures.