Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Understanding SQL Server inserted and deleted tables for DML triggers


By:   |   Read Comments (4)   |   Related Tips: More > 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


Last Update:





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

View all my tips





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, July 08, 2015 - 2:33:13 PM - Everett Back To Top

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 02, 2015 - 2:40:18 AM - Shakeer Back To Top

Thanks for great work


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

Nice Article dude..


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

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


Learn more about SQL Server tools