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
| Trigger | Stored 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 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.
