A trigger is a block of code that constitutes a set of T-SQL statements that are activated in response to certain actions. A trigger can also be interpreted as a special kind of stored procedure that is executed whenever an action, such as data modification, takes place.
A trigger is always defined on a table, and is said to have fired whenever the data in the underlying table is affected by any of the Data manipulation Language (DML) statements – INSERT, UPDATE or DELETE. A trigger fires in response to an event like insertion, updation, and deletion of data.
Triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex actions and cascade these actions to other dependent tables.
Characteristics of a Trigger
- It can only be associated with tables.
- It cannot be defined on temporary tables or views. However, it can reference temporary table views.
- It is fired automatically by SQL Server whenever any data modification statement is issued.
- It cannot be explicitly invoked or executed, as in the case of stored procedures.
- Triggers can be nested up to 16 levels. The nesting of triggers occurs when a trigger performs an action that initiates another trigger.
- It prevents incorrect, unauthorized, and inconsistent changes in data.
- It cannot return data to the user.
Creating Triggers
Triggers can be created by using the CREATE TRIGGER statement.
For example:
CREATE TRIGGER triggername ON tablename FOR [INSERT | DELETE | UPDATE]
[WITH ENCRYPTION]
AS sql_statements
WHERE
FOR [INSERT|UPDATE|DELETE} specifies which database modification statement will activate the trigger.
WITH ENCRYPTION encrypts the entries made in the syscomments table, which displays the text of the trigger that has been created.
AS sql_statements specifies the trigger conditions and actions. A trigger can contain any number of T-SQL statements, provided these are enclosed within the BEGIN and END keywords.
Magic Tables
Whenever a trigger fires in response to the INSERT, DELETE or UPDATE statement, two special tables are created. These are the inserted and the deleted table. They are also referred to as magic tables. These are conceptual tables and are structurally similar to the table on which the trigger is defined (the trigger table).
The inserted table contains a copy of all the records that are inserted in the trigger table. The deleted table contains all the records that have been deleted from the trigger table. Whenever any updation takes place, the trigger uses both the inserted and deleted tables.
Types of Triggers
SQL Server supports the three types of triggers:
- The INSERT trigger.
- The DELETE trigger
- The UPDATE trigger.
The INSERT Trigger
An INSERT trigger is fired whenever an attempt is made to insert a row in the trigger table. When an INSERT statement is issued, a new row is added to both the trigger and inserted tables.
The DELETE Trigger
A DELETE trigger is fired whenever an attempt is made to delete a row from the trigger table. When a DELETE statement is issued, the specified rows from the trigger table are deleted and are added to the deleted table. The deleted and trigger tables do not have any rows in common, as in the case of the inserted and trigger tables.
There are three ways of implementing referential integrity using a DELETE trigger. These are:
- The cascade method – Deletes records from the dependent tables whenever a record is deleted from the master table.
- The restrict method – Restricts the deletion of records from the dependent tables whenever a record is deleted from the master table.
- The nullify method – Nullifies the values in specific columns of the dependent tables whenever a record is deleted from the master table.
The UPDATE Trigger
When an UPDATE trigger is fired, it uses two logical tables for its operations – the deleted table that contains the original rows (the rows with the values before updating), and the inserted table that stores the new rows (the modified rows). Only after all the rows get updated, are the deleted and inserted tables populated and the trigger fired.