Question: Write a note on trigger.
A database trigger is special stored procedure that is run when specific actions occur within a database. Most triggers are defined to run when changes are made to a table’s data. Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT, UPDATE, and DELETE.
A trigger (from the Dutch trekken, meaning to pull) is a lever which, when pulled by the finger, releases the hammer on a firearm. In a database, a trigger is a set of Structured Query Language (SQL) statements that automatically "fires off" an action when a specific operation, such as changing data in a table, occurs. A trigger consists of an event (an INSERT, DELETE, or UPDATE statement issued against an associated table) and an action (the related procedure). Triggers are used to preserve data integrity by checking on or changing data in a consistent manner.
Schema-level triggers:
- After Creation
- Before Alter
- After Alter
- Before Drop
- After Drop
- Before Insert
The Four main types of Triggers are:
- Row-level trigger: This gets executed before or after any column value of a row changes
- Column-level trigger: This gets executed before or after the specified column changes
- For each row type: This trigger gets executed once for each row of the result set affected by an insert/update/delete
- For each statement type: This trigger gets executed only once for the entire result set, but also fires each time the statement is executed.