In this post, we will discuss about the different types of triggers in the SQL Server.

What is a trigger in the SQL server?
Triggers in SQL Server are a type of stored procedure that is automatically executed when an event occurs in the database object.
DML triggers: A DML trigger is triggered when a user attempts to modify the data through a data manipulation language (DML) event such as INSERT, UPDATE, and DELETE statements on a table or view.
DML events are executed when any legitimate event occurs, regardless of whether or not table rows are affected.
DDL triggers: Data definition language (DDL) triggers are fire in response to a range of DDL events such as CREATE, ALTER, and DROP statements in Transact-SQL.
How To Create SQL Server Trigger For Update Event?
Let’s create a SQL server trigger for an update event that will get executed once a row is updated in a table.
Before creating a trigger, let’s first create sample tables for examples. Let’s create a table “tblEmployee” to keep employee personnel information and a table “tblEmployeeHistory” to contain each modified record.
SQL Server Script To Create “tblEmployee” Table
-- Creating a new table "tblEmployee"
CREATE TABLE tblEmployee
(
[Id] INT PRIMARY KEY,
[Name] NVARCHAR(30),
[Gender] VARCHAR(10),
[City] VARCHAR(50),
[DeptId] INT
)
-- Inserting data into the "tblEmployee" table.
INSERT INTO tblEmployee VALUES
(101,'Shekh Ali','Male','Delhi', 1),
(102,'Mark Adam','Male','New York', 1),
(103,'Julie Folden','Female','Chicago', 2);
-- SQL Script to create a table "tblEmployeeHistory"
CREATE TABLE tblEmployeeHistory
(
[Id] INT IDENTITY(1,1) PRIMARY KEY,
[AuditData] NVARCHAR(500),
[Date] DATETIME
)
-- Select records from the following tables
SELECT * FROM tblEmployee;
SELECT * FROM tblEmployeeHistory;
After executing the SQL statements referred to above, the following tables will be created in the database.

In general, DML triggers create two special (virtual) magic tables called INSERTED and DELETED with the same structure or scheme as their base table.
The INSERTED table contains newly inserted records; however, the DELETED table contains old records after updating or deleting rows in a specific table.
In the SQL server trigger for the update, the temporarily inserted and deleted tables are created.
Syntax: SQL Trigger For Update Query
-- SQL script for creating a DML trigger for update events.
-- Author: Shekh Ali
CREATE TRIGGER TR_TblEmployee_ForUpdate ON tblEmployee
FOR UPDATE
AS
BEGIN
-- Declare variables to hold the old and updated record
DECLARE @Id INT, @OldDeptId INT;
DECLARE @OldName NVARCHAR(30), @OldGender NVARCHAR(10), @OldCity NVARCHAR(30);
-- Variable to build the audit string
DECLARE @AuditString NVARCHAR(1000);
-- Select the corresponding Id from the inserted table
Select Top 1 @Id = Id FROM INSERTED;
-- Select the corresponding row from the deleted table
SELECT @OldName = Name, @OldGender = Gender,
@OldCity = City, @OldDeptId = DeptId
FROM DELETED WHERE Id = @Id;
-- Build the audit string dynamically
Set @AuditString = 'Employee with Id = ' + Cast(@Id as NVARCHAR(4)) + ' changed'+
+' Name: ' +@OldName +' Gender: ' +' City: '+@OldCity +' DeptId: '+ Cast(@OldDeptId as NVARCHAR(4));
INSERT INTO tblEmployeeHistory VALUES(@AuditString, getdate());
END
Steps to create SQL update trigger

-
- First, To create a trigger, use the CREATE TRIGGER keywords, then specify the unique trigger name (Example: TR_TblEmployee_ForUpdate).
- Second, the options FOR, INSTEAD OF, or AFTER specifies when the trigger will be executed either before or after a single or multiple rows are modified.
- Then use the keyword ON to specify the name of the table associated with the trigger.
- Finally, specify the trigger body started from AS BEGIN to END which contains one or more SQL statements to be executed once trigger fired.
After execution of the above-mentioned SQL statements, a new SQL trigger for an update command will be created in the table-level trigger directory.
Please see the below image for a reference.

Update record in a database table
Let’s try to update a record in the “tblEmployee” table.
-- Update query
UPDATE tblEmployee SET City='Mumbai' WHERE Id=101;
Immediately after executing the UPDATE statement, the AFTER UPDATE trigger launches, and the new and old data will be stored in the INSERTED and DELETED magic tables.
Now we will be able to see the updated record in the”tblEmployee” table and the old record in the “tblEmployeeHistory” table inserted by the after update trigger.

How To Create SQL Server Instead Of Update Trigger?
In SQL Server, the INSTEAD OF UPDATE Trigger is executed automatically instead of the actual UPDATE event on a table or a view.
Suppose we have an INSTEAD OF UPDATE trigger on a table “tblEmployee”.
When we attempt to update a row of the “tblEmployee” table, the following trigger will be executed automatically instead of the actual Update event.
Let’s put one condition inside the trigger, if someone tries to update the Id column from the “tblEmployee” table, then such record must not be allowed to update but raised an error message.
The update query will be only allowed to execute If any other columns are updated rather than the ID column.
Syntax: SQL Instead of update trigger
Following are the SQL statements used to create Instead of update triggers.
-- SQL script to create Instead Of Trigger.
-- Author: Shekh Ali
CREATE TRIGGER [dbo].[Tr_tblEmployee_InsteadOfUPDATE]
ON [dbo].[tblEmployee]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EmployeeId INT,@DeptId INT
DECLARE @Name VARCHAR(50), @City VARCHAR(50), @Gender VARCHAR(50)
SELECT @EmployeeId = Id,
@Name = [Name],
@City = City,
@Gender = Gender,
@DeptId = DeptId
FROM INSERTED ;-- Magic Table
IF UPDATE(Id)
BEGIN
RAISERROR('EmployeeId cannot be updated.', 16 ,1)
ROLLBACK
INSERT INTO tblEmployeeHistory
VALUES( 'EmployeeId ' + Cast(@EmployeeId as NVARCHAR(5)) + ' cannot be updated.',GETDATE())
END
ELSE
BEGIN
UPDATE [tblEmployee]
SET [Name] = @Name,
City = @City,
Gender = @Gender,
DeptId = @DeptId
WHERE Id = @EmployeeId
END
END
Let’s try to update the Id column in the table “tblEmployee”.
-- Update query
UPDATE tblEmployee SET Id=105 WHERE Id=101;
The following error message will be shown when the Id field is updated in the tblEmployee table.

The “tblEmployeeHistory” table is also updated through the SQL Instead of Update trigger

SQL For Insert Trigger-Example
Create an INSERT trigger that will add the newly added employee Id to the History table.
-- SQL FOR INSERT TRIGGER EXAMPLE
CREATE TRIGGER [dbo].[Tr_tblEmployee_INSERT]
ON [dbo].[tblEmployee]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EmployeeId INT
SELECT @EmployeeId = INSERTED.Id
FROM INSERTED
INSERT INTO tblEmployeeHistory
VALUES( 'EmployeeId ' + Cast(@EmployeeId as NVARCHAR(5))
+ ' Inserted.',GETDATE())
END
Let’s try to insert the following employee record into the “tblEmployee” table.
INSERT INTO tblEmployee VALUES (104,'Rajat Sharma','Male','Delhi', 1);
Once we execute the above Insert statement, the trigger will insert the employee id detail in the “tblEmployeeHistory” table.

How to Create Delete Trigger In SQL Server?
Let’s Create a trigger that will not allow the Delete operation on the table “tblEmployee”.
CREATE TRIGGER Tr_tblEmployee_Delete
ON tblEmployee
FOR DELETE
AS
BEGIN
PRINT 'DELETE OPERATION IS RESTRICTED.'
ROLLBACK TRANSACTION
END
Let’s try to DELETE the record of the employee with Id is 101 in the table “tblEmployee”.
-- Delete employee with Id 101
DELETE FROM tblEmployee WHERE ID = 101;
When we try to execute the above Delete statement, it gives us the following error.

Trigger Limitations In SQL Server
-
- CREATE TRIGGER must be the first statement in the batch and can apply to only one table.
A trigger is created only in the current database; however, a trigger can reference objects outside the current database. - If the trigger schema name is specified to qualify the trigger, qualify the table name in the same way.
- The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.
INSTEAD OF DELETE/UPDATE triggers can’t be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.
- CREATE TRIGGER must be the first statement in the batch and can apply to only one table.
FAQ
What are triggers and their types in the SQL server?
A trigger in an SQL Server is a special kind of stored procedure that is triggered automatically when a given event such as Insert, Update or Delete occurs against a table in a database.
Types of Triggers
1. Data Definition Language (DDL) triggers: CREATE, ALTER, DROP
2. Data Manipulation Language (DML) triggers: INSERT, UPDATE, DELETE
3. CLR triggers
4. and Logon triggers
What is after trigger in SQL Server?
The AFTER triggers in the SQL server are executed once the INSERT, UPDATE, or DELETE command is successfully executed on the database object.
Can triggers update views in SQL Server?
Yes, only by using Instead of trigger in the view.
How do I disable a specific trigger in SQL?
Syntax:
ALTER TABLE TableName DISABLE TRIGGER TriggerName;
Example:
ALTER TABLE tblEmployee DISABLE TRIGGER Tr_tblEmployee_Delete;
How do I enable a specific trigger in SQL?
Syntax:
ALTER TABLE TableName ENABLE TRIGGER TriggerName;
Example:
ALTER TABLE tblEmployee ENABLE TRIGGER Tr_tblEmployee_Delete;
For more information, see CREATE TRIGGER (MSDN)
Conclusion:
In this article, we have learned about the various types of triggers in SQL Server. I hope you enjoyed this post. If you have any questions, please post your questions in the comment section.