Magic tables in SQL Server

 

Magic Tables are invisible tables, created on MS SQLServer, during INSERT/UPDATE/DELETE operations on any table. These tables temporarily persists values before completing the DML statements.

Inserted Table and Updated Table are two Magic tables available in SQLServer.

“Inserted Table” usage in “Trigger on Insert action”:

When a record is inserted into any table, then that record will be added temporarily inserted into Inserted table, before inserting into the appropriate table. Following Trigger on INSERT action explains the usage of Inserted Table,

CREATE TRIGGER PaymentLogger
ON Payment
FOR INSERT
DECLARE @UserID int
DECLARE @PaymentStatus varchar(50)
SELECT @PaymentStatus = PaymentStatus, @UserID = UserID FROM INSERTED
INSERT INTO PaymentLog(UserID, PaymentStatus, Message) VALUES (@UserID, @PaymentStatus, ‘Inserted into Payment table’)

“Deleted Table” usage in “Trigger on Delete action”:

When a record is deleted from any table, then that record will be inserted temporarily into the Deleted table. Following Trigger on DELETE action explains the usage of Deleted Table.

CREATE TRIGGER PaymentLogger
ON Payment
FOR DELETE
DECLARE @UserID int
DECLARE @PaymentStatus varchar(50)
SELECT @PaymentStatus = PaymentStatus, @UserID = UserID FROM DELETED
INSERT INTO PaymentLog(UserID, PaymentStatus, Message) VALUES (@UserID, @PaymentStatus, ‘Deleted from Payment table’)

Inserted and Deleted Table usage in “Trigger on Update action”:

When a record is updated from any table, then,

1. New record updated will be added into the Inserted table, and
2. Old record will be added into Deleted table.

Following Trigger on UPDATE action explains the usage of Inserted and Deleted Tables,

CREATE TRIGGER PaymentLogger
ON Payment
FOR UPDATE
DECLARE @UserID int
DECLARE @OldPaymentStatus,  @NewPaymentStatus varchar(50)
SELECT @NewPaymentStatus = PaymentStatus, @UserID = UserID FROM INSERTED
SELECT @OldPaymentStatus = PaymentStatus FROM DELETED
INSERT INTO PaymentLog(UserID, PaymentStatus, Message) VALUES   (@UserID, @NewPaymentStatus , ‘Updated from Payment table – old payment   status’ + @OldPaymentStatus )