SQL Trigger

Introduction to Trigger

  • A trigger is a special kind of stored procedures that automatically executes when an event occurs in the database server.
  • Triggers executes when a user tries to modify data through a data manipulation language (DML) event, such as Insert, Delete, Update.
  • These triggers fire when any valid event is fired.
  • A Trigger that contains statement which causes the other triggers are known as Cascading Triggers.

Difference between Trigger and Stored Procedure

TriggerStored Procedure
Trigger is an act which is performed automatically before or after an event occurs.Stored procedure is a set of functionality which is executed when it is explicitly invoked.
It cannot accept parameters.It can accept parameters.
A trigger cannot return any value.A stored procedure can return a value.
It is executed automatically on some event.It needs to be explicitly called.
Triggers are used for insertion, update and deletion.Stored procedures are often using independently in the database.

Row level and Statement level trigger

Row level Trigger
  • Row level trigger get fired once for each row in a table affected by the statements.
  • These triggers are useful if the code in the trigger action depends on the data provided by the triggering statement or rows that are affected.
  • Row Level Trigger is fired each time when row is affected by Insert, Update or Delete command.
  • If statement doesn’t affect any row, then no trigger action happens.
Statement level Trigger
  • Statement level trigger gets fired once for each triggering statement.
  • This kind of trigger fires when a SQL statement affects the rows of the table.
  • The trigger activates and performs its activity irrespective of number of rows affected due to the SQL statement.
  • These triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
▲