Create Trigger for SQL Server Insert Update and Delete

Overview

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

Explanation

Sample Test Scenario

One of the most used scenarios for triggers is to maintain a log of changes in a given table. In such a case, there is no point in having three different triggers, each one firing on INSERT, UPDATE, and DELETE operations. Fortunately, we have the chance to use a 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, in 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 following code.

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 in the screen capture below, the trigger worked as expected.

Screen capture showing the trigger in action.

Additional Information

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *