SQL-Magic Table

Magic Tables in SQL Server?

In SQL Server, Magic Tables refer to the virtual tables that are automatically created by the SQL Server engine during the execution of INSERT, UPDATE, and DELETE operations. These tables are:

1. Inserted Table - Holds the new rows that are being inserted or updated.
2. Deleted Table - Holds the rows that are being deleted or the rows before they are updated.

Magic tables are primarily used in triggers to access the data being manipulated during the DML operation.

Example of Magic Tables in SQL Server

Scenario: Auditing Changes in a Table

Imagine you have a table named Employees where you want to log every update operation into an EmployeeAudit table to track changes.

Schema Setup

-- Create the main table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

-- Create the audit table
CREATE TABLE EmployeeAudit (
    AuditID INT IDENTITY PRIMARY KEY,
    EmployeeID INT,
    OldFirstName NVARCHAR(50),
    OldLastName NVARCHAR(50),
    OldDepartment NVARCHAR(50),
    NewFirstName NVARCHAR(50),
    NewLastName NVARCHAR(50),
    NewDepartment NVARCHAR(50),
    ChangeDate DATETIME DEFAULT GETDATE()
);

Trigger to Use Magic Tables

-- Create an AFTER UPDATE trigger
CREATE TRIGGER trg_AuditEmployeeUpdates
ON Employees
AFTER UPDATE
AS
BEGIN
    -- Insert changes into the audit table
    INSERT INTO EmployeeAudit (
        EmployeeID,
        OldFirstName, OldLastName, OldDepartment,
        NewFirstName, NewLastName, NewDepartment,
        ChangeDate
    )
    SELECT
        d.EmployeeID,
        d.FirstName AS OldFirstName, d.LastName AS OldLastName, d.Department AS OldDepartment,
        i.FirstName AS NewFirstName, i.LastName AS NewLastName, i.Department AS NewDepartment,
        GETDATE() AS ChangeDate
    FROM
        Deleted d
    INNER JOIN
        Inserted i
    ON
        d.EmployeeID = i.EmployeeID;
END;

Explanation

• Inserted Table - Contains the new values of the rows being updated.
• Deleted Table - Contains the old values of the rows being updated.
• The INNER JOIN between Deleted and Inserted ensures you can compare the old and new values for each updated row.

Testing the Trigger

-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Doe', 'HR');

-- Update the data
UPDATE Employees
SET FirstName = 'Jonathan', Department = 'IT'
WHERE EmployeeID = 1;

-- Check the audit table
SELECT * FROM EmployeeAudit;

Output in EmployeeAudit

EmployeeAudit