Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Understanding SQL Server inserted and deleted tables for DML triggers

MSSQLTips author K. Brian Kelley By:   |   Read Comments   |   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: 3/22/2011


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


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.