DML Triggers in TSQL

There are two types of DML triggers in SQL Server. First type was AFTER/FOR trigger and the other was INSTEAD OF trigger. Triggers are extremely useful for logging different database operations (Insert/Update/Delete) into audit tables. Apart from afore said reason, triggers are helpful in using contextual data from different business operations on specific operations like aggregations/statistics.

We must be careful in planning DML triggers around different tables, because there can be infinite deadlock. For example, if an INSTEAD OF trigger causes an AFTER trigger to fire, and in return if that particular AFTER trigger cause INSTEAD OF trigger to fire again. This will lead to Direct Recursion and will cause error.

In below example (code sample), we are going to use AFTER INSERT trigger on Employee table. Every row which is being inserted into employees table will be checked for admin role in the trigger. In success scenarios, trigger is going to insert the same row to the Administrators table. Then we have INSTEAD OF INSERT trigger on Administrators table, so that this trigger is going to override the insert operation on Administrators table with a business check of having single administrator. Let’s say the business check is to have only one admin at a time, so for the first Admin insert INSTEAD OF trigger is going to insert the record, but for consecutive admin inserts it is going to raise error.

Code is self-explanatory. Comments are provided as and when required for better understanding.

/******************************** Create TestDB Database *******************************/ 
IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO
 
USE [TestDB]
GO
 
 
/******************************** Create Employee Table **********************************/
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Employees')
BEGIN
    DROP TABLE [dbo].[Employees];
END
GO
 
CREATE TABLE [dbo].[Employees](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](32) NOT NULL,
    [EmpRole] [nvarchar](32) NULL,
       [InsertedDate] [datetime] NOT NULL
)
GO
 
 
/********************************* Create Admins Table ************************************/ 
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Administrators')
BEGIN
    DROP TABLE [dbo].[Administrators];
END
GO
 
CREATE TABLE [dbo].[Administrators](
    [AdminId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](32) NOT NULL,
    [InsertedDate] [datetime] NOT NULL
)
GO
 
 
/********************************** Create After Trigger *************************************
The normal insert operation will be carried out on Employees Table, then After Insert trigger
is going to fire and check whether the inserted employee is of Admin role. If so, the same
record will be inserted into Administrators Table.
*********************************************************************************************/
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'TR' AND name = 'AfterInsertTrigger')
BEGIN
    DROP TRIGGER [dbo].[AfterInsertTrigger];
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[AfterInsertTrigger]
   ON  [dbo].[Employees]
   AFTER INSERT
AS
BEGIN
 
    IF((SELECT EmpRole FROM Inserted) = 'Admin')
    BEGIN
        INSERT 
        INTO 
                [dbo].[Administrators]
                (
                    [EmpName],
                        [InsertedDate]
                )
        VALUES
                (
                        (SELECT EmpName FROM Inserted),
                        GETDATE()
                );
    END
 
END
GO
 
 
/******************************** Create Instead of Trigger **********************************
InsteadOf Trigger takes the charge of normal insert operation to Administrators table. So in-
stead of normal insert operation, InsteadOf trigger will fire. In this trigger we check the
count of Administrators and if the count is more than 1, then it will raise error saying that
there can be only on administrator at a time. On the other side, if the admins count is less
than 1, this InsetedOf trigger is going to insert the row in Administrator table.
*********************************************************************************************/
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'TR' AND name = 'InsteadOfInsertTrigger')
BEGIN
    DROP TRIGGER [dbo].[InsteadOfInsertTrigger];
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[InsteadOfInsertTrigger]
   ON  [dbo].[Administrators]
   INSTEAD OF INSERT
AS
BEGIN
 
       IF((SELECT COUNT(*) FROM [dbo].[Administrators]) >= 1)
        BEGIN
            RAISERROR (N'Cannot have more than one Admin', 
                        16,
                        1); 
        END
       ELSE
        BEGIN
            INSERT 
            INTO 
                    [dbo].[Administrators]
                    (
                        [EmpName],
                        [InsertedDate]
                    )
            VALUES
                    (
                        (SELECT EmpName FROM Inserted),
                        GETDATE()
                    );
        END
       
END
GO
 
 
/********************************** Test After Trigger **************************************/
INSERT
INTO
        [dbo].[Employees]
        (
            [EmpName],
            [EmpRole],
            [InsertedDate]
        )
VALUES
        (
            'Rami',
            'Admin',
            GETDATE()
        );
 
 
/********************************** Test Instead Trigger ************************************/
INSERT
INTO
        [dbo].[Employees]
        (
            [EmpName],
            [EmpRole],
            [InsertedDate]
        )
VALUES
        (
            'Venu',
            'Admin',
            GETDATE()
        );
 
 
/********************************* Check Data from Tables ***********************************/
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[Administrators];
 
 
/********************************** Drop TestDB Database ************************************/
USE master
GO
 
IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

Output –

DmlTriggers1

 

Error which would outcome because of business validation of having only one admin from InsteadOf Trigger. Check picture below –

DmlTriggers2

You may also like...