By: K. Brian Kelley | Updated: 2011-03-22 | 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
- Read up on this tip about INSTEAD OF triggers and understand how they work.
- Know how to manipulate trigger firing order when more than one applies to an operation on a table.
About the author
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
Article Last Updated: 2011-03-22
