Post
Ask Question

Triggers in SQL Server

Introduction

In this article, we will learn about triggers,  types of triggers, and the benefits of triggers in SQL Server.

Triggers

SQL Server triggers are similar to stored procedures which are called automatically when a data modification event occurs against a table. It will execute before/after the Insert, Update or Delete in the table. The Trigger is called a special stored procedure because it cannot be called directly like a normal stored procedure. 

Types of Triggers

There are four types of triggers in the SQL Server. 

  1. DML Triggers - Data Manipulation Language Triggers.
  2. DDL Triggers - Data Definition Language Triggers
  3. CLR triggers - Common Language Runtime Triggers
  4. Logon triggers

DML Triggers

DML triggers are executed automatically when a DML event (data is inserted/updated/deleted in the table) occurs in the table. 

Syntax 

CREATE TRIGGER [Schema_Name].[Trigger_Name]
ON {Table_Name | View_Name}
{FOR|AFTER|INSTEAD OF} {[INSERT / UPDATE / DELETE]}
AS
BEGIN
    //SQL_Statements
END

Here,

Table_Name - Indicates the table to which the trigger applies.
FOR/AFTER - Indicates that the trigger has been executed only after the SQL statements are executed against the table. Here FOR and AFTER both are same.
INSTEAD OF - Indicates that the trigger is executed on behalf of the triggering SQL statement.
INSERT, UPDATE, DELETE -  The INSERT, UPDATE, DELETE specifies that the trigger will be executed against these operations on the table. We have to specify at least one option or the combination of multiple options.

Let's consider the below Employee table.

CREATE TABLE Employee
(
  Id int Primary Key,
  Name nvarchar(30),
  Salary int,
  Gender nvarchar(10)
)
GO
INSERT INTO Employee VALUES (1,'Sathya', 5000, 'Female');
INSERT INTO Employee VALUES (2,'Kavin', 2000, 'Male');
INSERT INTO Employee VALUES (3,'Anbu', 3000, 'Male');
INSERT INTO Employee VALUES (4,'Naga', 4000, 'Male');
INSERT INTO Employee VALUES (5,'Krishna', 5000, 'Female');
GO

After Insert Trigger

Let's create After Insert DML Trigger on the Employee table.

CREATE TRIGGER dbo.Trg_Insert_Employee
ON dbo.Employee
AFTER INSERT
AS
BEGIN
    Insert into dbo.audit (Id, ModifiedDate, [Type]) 
    Select inserted.Id, getdate(), 'Insert' FROM inserted;
END

The above trigger will be executed whenever new data have been inserted into the table. This trigger will insert the newly added id (in the Employee table) and date into audit table. Let’s try to insert the record into Employee table.

INSERT INTO dbo.Employee VALUES (6,'Test', 6600, 'Male');

When you try to execute the above Insert statement, first the INSERT statement is executed and immediately the trigger has fired. It will insert the data (which data have been inserted in the Employee table) into the audit table.

 

After Delete Trigger

Let's create After Delete DML Trigger on the Employee table.

CREATE TRIGGER dbo.Trg_Delete_Employee
ON dbo.Employee
AFTER DELETE
AS
BEGIN
    Insert into dbo.audit (Id, ModifiedDate, [Type]) 
    Select deleted.Id, getdate(), 'Delete' FROM deleted;
END

The above trigger will be executed whenever the delete event occurred in the table. This trigger will insert the deleted id (in the Employee table) and date into audit table. Let’s try to delete the record in the Employee table.

Delete from dbo.Employee Where Id = 1;

When you try to execute the above Delete statement, first the DELETE statement is executed and immediately the trigger has fired. It will insert the data (which data has been deleted in the Employee table) into the audit table.

 

Update Trigger

Let's create Update DML Trigger on the Employee table.

CREATE TRIGGER dbo.Trg_Update_Employee
ON dbo.Employee
FOR UPDATE
AS
BEGIN
    Print 'You Cannot Perform Update Operation'
    Rollback Transaction
END

The above trigger should restrict the UPDATE operation on the Employee table. Let’s try to update records in the Employee table.

UPDATE dbo.Employee SET Salary = 50000 WHERE Id = 1

If you are trying to update the data in the Employee table, then it will show the below error. First, the UPDATE statement is executed and immediately the trigger has fired. It will roll back the UPDATE operation and print the error message.

Instead of Trigger

"Instead of" trigger executes instead of the actual DML events such as INSERT, UPDATE or DELETE events. If we try to execute any DML statements such as Insert, Update, and Delete, then on behalf of the DML statement, the instead of trigger will be executed.

CREATE TRIGGER dbo.Trg_InsteadOf_Insert_Employee 
ON dbo.Employee
INSTEAD OF DELETE
AS
BEGIN
  Select 'Sample Instead of trigger' as [Message]
END

The above trigger will execute before the delete event occurs in the table. Let’s try to delete the record in the Employee table.

Delete from dbo.Employee Where Id = 1;

When you try to execute the above Delete statement, First the trigger has fired. It will print the message and Delete operation won't be happening.

The list of trigger of the table are available under Triggers section under the Table Name.

DDL Trigger

DDL triggers are executed automatically when a DDL event (CREATE, ALTER, DROP, GRANT, DENY and REVOKE) are occurring in SQL Server. The DDL triggers are executed only after the DDL statements are executed. It cannot use the “Instead Of Triggers”.

The advantages of DDL triggers are, 

  • To track the changes in the database schema.
  • Restrict the changes to the database schema.
  • Respond to a change in the database schema.

Syntax 

CREATE TRIGGER [Schema_Name].[Trigger_Name]
ON { DATABASE |  ALL SERVER }
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {SQL_Statements}

Let’s create the below DDL trigger for tracking creating, dropping, and altering the table.

CREATE TRIGGER dbo.Trg_Table
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
BEGIN
  INSERT INTO dbo.Logs SELECT EVENTDATA();
END

If you are trying to create the table in the database, first the DML statement is been executed, and immediately the trigger is fired. It will insert the data (event data) into the Logs table.

The event data looks like as follows,

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2022-02-23T11:36:18.763</PostTime>
  <SPID>57</SPID>
  <ServerName>Test</ServerName>
  <LoginName>TestUser</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>Test</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TestTable</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>Create Table dbo.TestTable (
	Id int Primary Key,
	[name] nvarchar(100)
)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

LOGON Triggers

LOGON triggers fires in response to a LOGON event. LOGON triggers fire after successful authentication and before establishing the user session.

LOGON triggers are created at the server level and it is used to,

  • To audit login activity
  • To control the login activity

Disable the Triggers 

The DISABLE TRIGGER statement is used to disable a trigger.

The below syntax is used to disable the particular trigger.

DISABLE TRIGGER [Schema_Name].[Trigger_Name] 
ON [Object_Name | DATABASE | ALL SERVER]

Example

DISABLE TRIGGER dbo.Trg_Insert_Employee ON dbo.Employee;

The below syntax is used to disable the all trigger on a table,

DISABLE TRIGGER ALL ON Table_Name;

Example :

DISABLE TRIGGER ALL ON dbo.Employee;

The below syntax is used to disable all triggers on a database,

DISABLE TRIGGER ALL ON DATABASE;

Enable the Triggers

The ENABLE TRIGGER statement is used to enable a trigger.

The below syntax is used to enable the particular trigger.

ENABLE TRIGGER [Schema_Name].[Trigger_Name] 
ON [Object_Name | DATABASE | ALL SERVER]

Example

ENABLE TRIGGER dbo.Trg_Insert_Employee ON dbo.Employee;

The below syntax is used to disable all triggers on a table,

ENABLE TRIGGER ALL ON Table_Name;

Example :

ENABLE TRIGGER ALL ON dbo.Employee;

The below syntax is used to disable all triggers on a database,

ENABLE TRIGGER ALL ON DATABASE;

Remove/Drop the Triggers

The DROP TRIGGER statement is used to drops one or more triggers from the database.

The below syntax is used to drop the DML triggers.

DROP TRIGGER [ IF EXISTS ] [Schema_Name.]Trigger_Name [ ,...n ];

The below syntax is used to drop the DDL triggers.

DROP TRIGGER [ IF EXISTS ] Trigger_Name [ ,...n ] ON { DATABASE | ALL SERVER };

The below syntax is used to drop the Logon triggers.

DROP TRIGGER [ IF EXISTS ] Trigger_Name [ ,...n ] ON ALL SERVER;

Example

DROP TRIGGER IF EXISTS dbo.Trg_Insert_Employee;

Advantages of Triggers

  • Triggers are used to validate data before being inserted or updated.
  • Triggers are used to set rules and roll back statements, if any changes do not satisfy those rules.
  • Triggers are used for logging the changes of records.
  • Triggers are easy to code and easy to maintain.
  • Can call stored procedures and functions from inside a trigger.

Disadvantages of Triggers

  • Triggers are not fired on BULK INSERTS unless you include the FIRE_TRIGGERS option in the bulk insert statement.
  • Triggers add overhead to DML statements. Triggers run every time the data are inserted or updated which is overhead on the system. It makes the system run slower.
  • Triggers are executed automatically and their execution is invisible to the client application. Therefore, it isn't easy to identify what happens in the database layer. If there is no documentation about triggers, then it will be difficult to figure out for new developers.
  • If there are many nested triggers, then it will be very hard to debug. It consumes development time.

Summary

In this article, you have learned about the below topics,

  • What is a Trigger
  • Types of Triggers 
  • How to create and remove the Triggers
  • How to enable and disable the Triggers
  • Advantages and Disadvantages of Triggers


Similar Articles