By: Daniel Farina
Overview
In the previous chapter of this tutorial we created a hello world style trigger. Now if we want to make a trigger for a more useful real world case, we need to be aware of the data being inserted into the base table or view. For that purpose, SQL Server has the INSERTED table which can be used to get the exact data.
The INSERTED Table
SQL Server automatically creates this table to keep copies of the affected rows during INSERT and UPDATE statements. Basically it is like a temporary table that mirrors the structure of the table that owns the trigger. Every time you perform an INSERT or UPDATE statement the new data is stored in the inserted pseudo table. This allows us to find differences between the state of a table before and after a data modification and take actions based on that difference, like when you need to enforce business rules.
In order to see how it works let's create a sample table as follows and add a row to it.
CREATE TABLE Test_PseudoTables
(
ItemID INT IDENTITY(1, 1) PRIMARY KEY ,
ItemText VARCHAR(50)
);
GO
INSERT INTO dbo.Test_PseudoTables ( ItemText )
VALUES ( 'Hello World' );
GO
The following trigger fires on every insert statement that impacts the Test_PseudoTables table. This trigger's only function is to show the contents of the inserted pseudo table by using a SELECT statement.
CREATE TRIGGER TR_I_Test_PseudoTables
ON dbo.Test_PseudoTables
FOR INSERT
AS
SELECT I.ItemID,
I.ItemText
FROM Inserted I;
GO
Let's see what happens when we run the following code.
SELECT * FROM dbo.Test_PseudoTables; INSERT INTO dbo.Test_PseudoTables ( ItemText ) VALUES ( 'MSSQLTips.com' ); SELECT * FROM dbo.Test_PseudoTables;
As you can see in the image above, the row we inserted into the table is shown when we do a SELECT from the inserted pseudo table that occurs within the trigger.
