Understanding SQL Server inserted and deleted tables for DML triggers

By:   |   Comments (7)   |   Related: > Triggers


Problem

I'm trying to put triggers on an existing database, but I am having a hard time understanding how to use the inserted and deleted tables. They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update? And do they contain a single row per row of data changed or are they truly tables?

Solution

Let's answer the second question first. These two tables (inserted and deleted), if they contain anything, contain a row for every row of data affected and therefore should be treated as tables. The trigger only fires once per operation under normal circumstances. And since they should be treated as tables, use set-based operations on them whenever you can. Too many times we see cursors going row-by-row through these tables when a set based operation would have been better.

Now let's talk about when these tables are used and what data they contain:

Operation deleted Table inserted Table
INSERT (not used) Contains the rows being inserted
DELETE Contains the rows being deleted (not used)
UPDATE Contains the rows as they were before the UPDATE statement Contains the rows as they were after the UPDATE statement

A conceptual way of thinking how the deleted and inserted tables are used in an UPDATE operation is that SQL Server first deletes the old rows, populating the deleted table, and then inserts the new and modified rows, populating the inserted table. While this isn't what actually happens, it helps visualize what the tables contain and is useful in that sense. Let's see an example of the triggers in operation with these tables.

First, the setup:

USE MSSQLTips;
GO

CREATE TABLE dbo.SampleTable (
  
SampleTableID INT NOT NULL IDENTITY(1,1),
  
SampleTableInt INT NOT NULL,
  
SampleTableChar CHAR(5) NOT NULL,
  
SampleTableVarChar VARCHAR(30) NOT NULL,
  
CONSTRAINT PK_SampleTable PRIMARY KEY CLUSTERED (SampleTableID)
);
GO

CREATE TABLE dbo.SampleTable_Audit (
  
SampleTableID INT NOT NULL,
  
SampleTableInt INT NOT NULL,
  
SampleTableChar CHAR(5) NOT NULL,
  
SampleTableVarChar VARCHAR(30) NOT NULL,
  
Operation CHAR(1) NOT NULL,
  
TriggerTable CHAR(1) NOT NULL,
  
AuditDateTime smalldatetime NOT NULL DEFAULT GETDATE(),
);

CREATE INDEX IDX_SampleTable_Audit_AuditDateTime ON dbo.SampleTable_Audit (AuditDateTime DESC);
GO

CREATE TRIGGER dbo.SampleTable_InsertTrigger
ON dbo.SampleTable
FOR INSERT
AS
BEGIN
   INSERT INTO
dbo.SampleTable_Audit
  
(SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, Operation, TriggerTable)    
  
SELECT SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, 'I', 'I'
  
FROM inserted;
END;
GO

CREATE TRIGGER dbo.SampleTable_DeleteTrigger
ON dbo.SampleTable
FOR DELETE
AS
BEGIN
   INSERT INTO
dbo.SampleTable_Audit
  
(SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, Operation, TriggerTable)    
  
SELECT SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, 'D', 'D'
  
FROM deleted;
END;
GO

CREATE TRIGGER dbo.SampleTable_UpdateTrigger
ON dbo.SampleTable
FOR UPDATE
AS
BEGIN
   INSERT INTO
dbo.SampleTable_Audit
  
(SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, Operation, TriggerTable)    
  
SELECT SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, 'U', 'D'
  
FROM deleted;
  
  
INSERT INTO dbo.SampleTable_Audit
  
(SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, Operation, TriggerTable)    
  
SELECT SampleTableID, SampleTableInt, SampleTableChar, SampleTableVarChar, 'U', 'I'
  
FROM inserted;
END;
GO

Now let's test the triggers:

USE MSSQLTips;
GO

-- First the inserts
INSERT INTO dbo.SampleTable
(SampleTableInt, SampleTableChar, SampleTableVarChar)
VALUES
(1, '11111', '1111111111');

INSERT INTO dbo.SampleTable
(SampleTableInt, SampleTableChar, SampleTableVarChar)
VALUES
(2, '22222', '222222222222222');

INSERT INTO dbo.SampleTable
(SampleTableInt, SampleTableChar, SampleTableVarChar)
VALUES
(3, 'AAAAA', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
GO

-- Check the sample table
SELECT * FROM dbo.SampleTable;
GO

-- Check the inserts
SELECT * FROM dbo.SampleTable_Audit;
GO

-- Perform a delete operation
DELETE FROM dbo.SampleTable
WHERE SampleTableInt = 2;
GO

-- Check the sample table
SELECT * FROM dbo.SampleTable;
GO

-- Check the delete
SELECT * FROM dbo.SampleTable_Audit;
GO

-- Perform an update operation
UPDATE dbo.SampleTable
SET SampleTableChar = '33333'
WHERE SampleTableInt = 3;
GO

-- Check the sample table
SELECT * FROM dbo.SampleTable;
GO

-- Check the update
SELECT * FROM dbo.SampleTable_Audit;
GO

This should give you a good idea of how the tables work for the various operations. The only tricky one is the UPDATE operation, and if you notice from the example, the old values are in the deleted table and the new values are in the inserted table, just as was predicted.

Now there is a catch you should be aware of with AFTER triggers (the default type of trigger unless you manually specify that's an INSTEAD OF trigger). The text, ntext, and image types are not supported in AFTER triggers, but are available in INSTEAD OF triggers, so if you have to deal with columns that are of these types, you'll need to build an INSTEAD OF trigger. As of SQL Server 2005, these data types are included for backward compatibility only.  The new data types to use are VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) respectively.  Any new database design should make use of these new data types.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, May 16, 2018 - 12:43:15 PM - Gabriel Issah Back To Top (75956)

 Simple said: You are the best. No wonder you write Sql server Bibles. I have spent months trying to figure the update trigger part out. until this morning I found this and in less than 10 minutes. I can write my own complex update triggers.

Once again you are the best


Tuesday, April 3, 2018 - 10:45:08 AM - Isis Nobre Back To Top (75591)

 I wanna congratulate and thank the author for this very clear and helpful article! These tips helped me a lot to solve some critical problems, so again I am very grateful for it! Great work! 

 


Thursday, September 1, 2016 - 3:22:16 AM - Sunil Back To Top (43243)

Your article are very comprehensible and easy understand.

Thanks a lot!

keep writing!

 

 


Wednesday, July 8, 2015 - 2:33:13 PM - Everett Back To Top (38156)

Brian:

   I am curious about the state of inserted and deleted while a trigger is firing. 

   I have an after insert trigger on table A which updates a column on table B based upon an inner join on table A.  There will be 0 to many rows updated in table B.  After table B is updated, I want to update a date_completed column in table A, but I only want to update those rows that were affected in table B.

 

   My gut tells me that the inserted table only has the original data in it (from table A), not the data that was updated in table B.  Is this true? 

   The reason that I don't just update the date_completed in table A joining on table B is that table A may already have a value in this column.

 

   Thank you in advance for your attention.

 

m

 

 

 


Tuesday, June 2, 2015 - 2:40:18 AM - Shakeer Back To Top (37352)

Thanks for great work


Friday, September 12, 2014 - 3:05:35 AM - Mani Kiran Thota Back To Top (34505)

Nice Article dude..


Thursday, July 10, 2014 - 11:17:56 AM - sumit Back To Top (32633)

Sir, Thank you so much... for this example. Good day sir.. :-)















get free sql tips
agree to terms