A Trigger in SQL Server is a database object.SQL Server contains many different objects which either store data or provide access to the data.Other common database objects are:
- Stored Procedures
Trigger has a similarity with a stored procedure.Both Stored Procedure and Trigger
contains T-SQL statements which are executed as a unit.But Triggers and Stored Procedures differs in how they are executed.
Stored Procedure needs to be explicitly executed.If Stored Procedure expects input parameters then they also needs to be explicitly passed.Unlike Stored Procedure Trigger is not explicitly called.Instead trigger is executed in response to certain actions in the database.
Some common uses of triggers are:
- To keep the record of the changes in a table.Keeping record of changes called audit
trail or change tracking is commonly implemented using triggers.
- To verify the data that is being inserted or updated in a table.We can ensure that only
valid data is inserted.
- Triggers are also commonly used to enforce referential integrity.
There are different types of triggers.Triggers can be classified based on the different types of SQL Statements against which they are fired.
DDL statements are those statements which are used to create or modify the database objects.Some of the DDL Statements are:
DDL Triggers are those triggers which fire in response to the DDL statements which modifies the structure of the database objects such as create table or drop table.
DML Statements are the statements which are used to manage the data in the database.DML statements are:
DML Triggers are those triggers which fire in response to INSERT,UPDATE and DELETE statements.So DML triggers can be defined for these types of statements.
A DML Trigger is always attached to a table or a View.While creating a Trigger it is attached to a table or a view.Trigger fires when the data is modified or deleted from the table or the view. DML Triggers can be of two types depending on the moment the Trigger fires.
Triggers can execute either after the triggering action or instead of the triggering action.If the Trigger executes after the SQL statement then they are called After Triggers while a Trigger which executes in place of the triggering action is called Instead of Trigger.
Let’s see how to define different types of DML triggers
AFTER TRIGGER can be defined for each type of DML statment:
- AFTER INSERT Trigger Fires after the data is inserted in the table
- AFTER UPDATE Trigger Fires after the data is updated in the table
- AFTER DELETE Trigger Fires after the data is deleted from the table
After trigger can not be defined for a view.
We can define After trigger as:
CREATE TRIGGER TriggerName
FOR [INSERT / UPDATE / DELETE]
BODY OF THE TRIGGER
We can use either the FOR or AFTER statement to create after trigger.Both FOR or AFTER keyword can be used to create after trigger
Example of After Insert trigger
We can create a Trigger using the SQL Management Studio.As a Trigger is always associated with a table or a view so we find an option for triggers in a Triggers folder in the Tables node of the database
To create a Trigger we right click and select the “New Trigger” option
Clicking on the New Trigger option will display a template to create a trigger in the query editor window:
To create After Insert trigger we will modify the generated template.We will be creating after trigger which will insert values in the audit table whenever insert statement is fired on the Users table.We will be inserting values in the UserAudit table for every insert action.
CREATE TABLE [dbo].[Users](
[UserName] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[Id] [int] NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
We can create the UserAudit table using the following script:
CREATE TABLE [dbo].[UserAudit](
[UserName] [varchar](50) NULL,
[UserAction] [varchar](50) NULL,
[ActionDate] [date] NULL
) ON [PRIMARY]
We create the trigger as:
CREATE TRIGGER Update_UserAudit
--SET NOCOUNT ON;
DECLARE @UserName varchar(50)
DECLARE @Email varchar(50)
SELECT @UserName = UserName
INSERT INTO [dbo].[UserAudit](UserName,UserAction,ActionDate)
Now if we insert a record in the Users table a new record will be inserted in the UserAudit table as well.If we execute the following insert statement
INSERT INTO [dbo].[Users]([UserName],[Email],[Password],[Id],[IsActive])
now if we check the UserAudit table we can see the following record
These triggers are executed before the original DML statement.For example INSTEAD OF DELETE Trigger will be executed before the delete statement.In place of the delete statement INSTEAD OF DELETE Trigger will be executed.Similar to FOR Trigger INSTEAD OF Trigger can also be defined for each of the DML statements
INSTEAD OF INSERT Trigger Trigger is fired in place of the actual insert statement
INSTEAD OF UPDATE Trigger Trigger is fired in place of the actual update statement
INSTEAD OF DELETE Trigger Trigger is fired in place of the actual delete statement
Example of Instead of Delete trigger
Instead of Delete trigger is used to enforce a check to verify if the valid rows are being deleted.We are defining a Instead of Delete trigger for the Users table.We are verifying the id of the row being deleted.If the id is 1 then the delete statement is rolled back.
CREATE TRIGGER Delete_UserAudit
INSTEAD OF DELETE
SET NOCOUNT ON;
DECLARE @Id INT
SELECT @Id =Id FROM DELETED
RAISEERROR('CAN NOT DELETE RECORD WITH ID 1',16,1);
DELETE FROM Users where Id=@Id