Create Trigger for SQL Server Insert Update and Delete

By: Daniel Farina

Overview

This chapter of the triggers tutorial will give an example on how to build a trigger that fires on every type of DML statement: insert, update and delete.

Sample Test Scenario

One of the most used scenarios for triggers is to maintain a log of changes in a given table. In such case, there is no point on having three different triggers, each one firing on INSERT, UPDATE and DELETE operations. Fortunately we have the chance to use one single trigger for all of those operations. The only drawback is that you have to add logic to know what event fired the trigger. With a bit of ingenuity and using the INSERTED and DELETED pseudo tables we can know the type of operation that fired the trigger by comparing the number of rows of each pseudo table.

To give you an example, suppose the INSERTED pseudo table contains rows and the DELETED pseudo table is empty then we know that the event that fired the trigger was an INSERT statement. In opposition, if the DELETED pseudo table has rows and the INSERTED pseudo table is empty then the statement that fired the trigger was a DELETE. But if both INSERTED and DELETED have rows then the statement that fired the trigger was an UPDATE.

Let's create a table to use in this example.

CREATE TABLE NestingTest
(
    NestingTestID INT IDENTITY(1, 1),
    Test INT NULL
);

Now on the code section below you will see a simple trigger that has the logic to determine which statement was responsible for firing the trigger and prints the statement type.

CREATE OR ALTER TRIGGER TR_IUD_NestingTest ON NestingTest
FOR INSERT, UPDATE, DELETE
AS
DECLARE @Operation VARCHAR(15)
 
IF EXISTS (SELECT 0 FROM inserted)
BEGIN
   IF EXISTS (SELECT 0 FROM deleted)
   BEGIN 
      SELECT @Operation = 'UPDATE'
   END ELSE
   BEGIN
      SELECT @Operation = 'INSERT'
   END
END ELSE 
BEGIN
   SELECT @Operation = 'DELETE'
END 
PRINT @Operation

To test the trigger we can use the code below.

INSERT INTO dbo.NestingTest
(Test) VALUES (0);
 
UPDATE dbo.NestingTest
SET Test = 1
WHERE NestingTestID = 1;
 
DELETE FROM dbo.NestingTest
WHERE NestingTestID = 1;

As you can see on the screen capture below the trigger worked as expected.

Additional Information

• How to find Updated Column in SQL Server Trigger