Magic Tables are virtual tables that are automatically created and maintained by SQL Server for each data modification operation performed on a table. They allow you to access the old and new values of the modified rows without writing any additional code.
Key Points:
- Magic tables are read-only.
- They are created automatically by SQL Server during the execution of triggers.
- You cannot modify or manually access these tables outside the scope of the trigger.
Example: Using Magic Tables in a Trigger
Here is an example of a trigger using magic tables:
CREATE TRIGGER trgAfterUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
-- Accessing the "Inserted" and "Deleted" magic tables
SELECT * FROM Inserted; -- New values after the update
SELECT * FROM Deleted; -- Old values before the update
-- Example: Log changes to an audit table
INSERT INTO EmployeeAudit (EmployeeID, OldSalary, NewSalary, ModifiedDate)
SELECT
d.EmployeeID,
d.Salary AS OldSalary,
i.Salary AS NewSalary,
GETDATE() AS ModifiedDate
FROM
Inserted i
INNER JOIN
Deleted d
ON
i.EmployeeID = d.EmployeeID;
END;
Operations and Magic Table Usage:
| DML Operation | Inserted Table | Deleted Table |
|---|---|---|
| INSERT | Contains new rows | Not available |
| DELETE | Not available | Contains deleted rows |
| UPDATE | Contains updated rows (new values) | Contains original rows (old values) |
The use of INSERTED and DELETED virtual tables in different scenarios
DECLARE @InsertedRows1 TABLE (EmpID INT);
INSERT INTO .
( lastname, firstname, title, titleofcourtesy, birthdate, hiredate, address, city, region, postalcode, country, phone, mgrid)
OUTPUT INSERTED.EmpID INTO @InsertedRows1
VALUES
( 'Davis', 'Sara', 'CEO', 'Ms.', '1958-12-08', '2002-05-01', '7890 - 20th Ave. E., Apt. 2A', 'Seattle', 'WA', '10003', 'USA', '(206) 555-0101', NULL);
SELECT * FROM @InsertedRows1;
-------------------------------------------------------------------------
DECLARE @InsertedRows2 TABLE (
NewID INT,
NewFirstName NVARCHAR(50),
OldID INT,
OldFirstName NVARCHAR(50)
);
UPDATE HR.Employees
SET firstname = 'Kashyap'
OUTPUT INSERTED.EmpID, INSERTED.firstname, DELETED.EmpID, DELETED.firstname INTO @InsertedRows2
WHERE EmpID = 1;
SELECT * FROM @InsertedRows2;
---------------------------------------------------------
DECLARE @InsertedRows3 TABLE (
OldID INT,
OldFirstName NVARCHAR(50)
);
DELETE HR.Employees
OUTPUT DELETED.EmpID, DELETED.firstname INTO @InsertedRows3
WHERE EmpID >=11
SELECT * FROM @InsertedRows3;