Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
 Different Types of  Triggers In SQL Server

Different Types of Triggers In SQL Server

16 Mar 2024
Intermediate
535K Views
14 min read

Triggers In SQL Server: An Overview

Triggers in SQL Server are database objects. These are a special type of stored procedure that is automatically fired/executed when a DDL or DML command statement related to the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, control server operations, audit a server, and implement business logic or business rules.

Read More: Basics of SQL Commands

Types of Triggers

In SQL Server we can create four types of triggers:

Types of Triggers

1. DDL Triggers

In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system-defined stored procedures that perform DDL-like operations.

Example

If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create a login user, then both these can execute/fire a DDL trigger that you can create on the CREATE_LOGIN event of SQL Server.

We can use only the FOR/AFTER clause in DDL triggers, not the INSTEAD OF clause, which means we can only make After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server and to audit these operations. DDL triggers can be used to manage administrative tasks such as auditing and regulating database operations.

Read More: SQL Server Interview Questions and Answers

2. DML Triggers

In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types:

DML Triggers

  1. After Trigger (using FOR/AFTER CLAUSE)

    This type of trigger fires after the SQL Server finishes the execution of the action successfully that fired it.

    After Trigger (using FOR/AFTER CLAUSE

    Example

    If you insert a record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like the primary key constraint, and some rules. If the record/row insertion fails, the SQL Server will not fire the After Trigger.

  2. Instead of Trigger (using INSTEAD OF CLAUSE)

    This type of trigger fires before the SQL Server starts execution of the fired action. This differs from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that was successfully executed but does not include the actual insert/update/delete to the table.

    Instead of Trigger (using INSTEAD OF CLAUSE)

    Example

    If you insert a record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as the primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

Read More: After Trigger, Instead of Trigger Example

3. CLR Triggers

CLR triggers are a special type of triggers based on the CLR (Common Language Runtime) in the .NET framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of the .NET languages like C#, Visual Basic, and F#.

We coded the objects(like triggers) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual Basic, and F#. I will discuss the CLR trigger later.

4. Logon Triggers

Logon triggers are a special type of trigger that fire when the LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is established. Hence, all messages we define in the trigger such as error messages, will be redirected to the SQL Server error log.

Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

Syntax for Logon Trigger


CREATE TRIGGER trigger_name
ON ALL SERVER
[WITH ENCRYPTION]
{FOR|AFTER} LOGON
AS
sql_statement [1...n ] 

Syntax for Trigger


CREATE TRIGGER trigger_name
ON {table|view} 
[WITH ENCRYPTION|EXECUTE AS] 
{FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 
[NOT FOR REPLICATION] 
AS 
sql_statement [1...n ]
  1. trigger_name

    This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.

  2. table|view

    This is the table/view on which the trigger is to be created.

  3. ENCRYPTION

    This option is optional. If this option is specified, the original text of the CREATE TRIGGER statement will be encrypted.

  4. EXECUTE AS

    This option is optional. This option specifies the security context under which the trigger is executed.

  5. FOR/AFTER

    FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified. AFTER triggers cannot be defined on views.

  6. INSTEAD OF

    INSTEAD OF specifies that the trigger is Instead Of Trigger.

  7. CREATE|ALTER|DROP|INSERT|UPDATE|DELETE

    These keywords specify which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.

  8. NOT FOR REPLICATION

    Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

  9. AS

    After this, we specify the actions and conditions that the trigger performs.

  10. sql_statement

    These are the trigger conditions and actions. The trigger actions are specified in the T-SQL statements.

Note

  1. The name of a trigger should follow the rules for identifiers.
  2. DML trigger can be composed by any T-SQL statement, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements.
  3. You cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions.
  4. If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server.
  5. AFTER triggers cannot be defined on views.
  6. AFTER is the default, if FOR is the only keyword specified.

Example of a Trigger


delimiter $$
CREATE TRIGGER  Check_age  BEFORE INSERT ON Employee 
FOR EACH ROW
BEGIN
IF NEW.age < 35 THEN
SIGNAL SQLSTATE '35000'
SET MESSAGE_TEXT = 'ERROR: 
         AGE MUST BE ATLEAST 35 YEARS!';
END IF;
END; $$
delimiter; 

In the above code, while inserting any tuple to the table ’Employee’, a trigger named ‘Check_age’ will be executed. This trigger will check the age attribute. If it is greater than 35 then this tuple will be inserted into the table otherwise an error message will be printed stating “ERROR: AGE MUST BE ATLEAST 35 YEARS!”

How to Show Triggers in SQL Server?

To show all the triggers in a SQL Server database, you need to query the sys.triggers system catalog view.


SELECT name AS TriggerName,
       OBJECT_SCHEMA_NAME(parent_id) AS SchemaName,
       OBJECT_NAME(parent_id) AS TableName,
       create_date AS CreationDate,
       modify_date AS LastModifiedDate
FROM sys.triggers;

This query retrieves information about each trigger in the database, including the trigger name, schema name, associated table name, creation date, and last modification date.

How to Update Triggers in SQL Server?

To update a trigger in SQL Server, we can use the ALTER TRIGGER statement.

Syntax to Update Triggers in SQL Server


ALTER TRIGGER trigger_name
ON table_name
[ AFTER | INSTEAD OF ] { [ INSERT ] [,] [ UPDATE ] [,] [ DELETE ] }
AS
BEGIN
    -- Trigger logic here
END;

Example to Update a Trigger in SQL


ALTER TRIGGER UpdateLastModified
ON Employees
AFTER INSERT, UPDATE
AS
BEGIN
    -- Updated trigger logic here
    SET NOCOUNT ON;
    IF UPDATE(EmployeeName) OR UPDATE(EmployeeSalary)
    BEGIN
        UPDATE Employees
        SET LastModified = GETDATE()
        WHERE EmployeeID IN (SELECT EmployeeID FROM inserted)
    END
END;

In the above code, the trigger UpdateLastModified is being updated, and the trigger logic inside the BEGIN...END block is modified.

Advantages of Triggers

  • They enforce data integrity.
  • They are easy to maintain.
  • You can call stored procedures and functions from inside a trigger.
  • You can use external code as a trigger by using CLR triggers.
  • Triggers are easy to code.

Disadvantages of Triggers

  • Triggers need to be properly documented.
  • Triggers add overhead to DML statements.
  • Triggers only allow using extended validations.
  • Triggers can be difficult to debug since the code is executed automatically without any user input.
  • As there is no control over when and how the trigger will be executed, finding and solving errors is difficult
Summary

In this article, I tried to explain the types of SQL Server triggers. I hope after reading this article your SQL trigger concepts will be strong. I will discuss all these triggers with an example in my next post. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

FAQs

Q1. How many triggers are possible in SQL?

In SQL Server, there isn't a predefined limit on the number of triggers that can be created in a database.

Q2. How many types of trigger exist?

1. DDL Triggers
2. DML Triggers
3. CLR Triggers
4. Logon Triggers

Q3. How many types of DML triggers are present in SQL?

There are two types of DML triggers present in SQL.

Q4. What is the difference between DDL and DML triggers?

DDL triggers fire in response to Data Definition Language (DDL) events (e.g., CREATE, ALTER, DROP), while DML triggers fire in response to Data Manipulation Language (DML) events (e.g., INSERT, UPDATE, DELETE).

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this