MS SQL Server 23 - Magic Tables in SQL Server

In SQL Server, "Magic Tables" refer to the special tables (INSERTED and DELETED) that hold copies of data affected by DML (Data Manipulation Language) operations like INSERT, UPDATE, and DELETE. These tables are particularly useful in triggers and provide a way to access and analyze data before and after changes occur. Let’s explore how Magic Tables work with a step-by-step example.

What are Magic Tables?

Magic Tables are virtual tables automatically created by SQL Server for use within triggers. They help track the old and new values of rows affected by DML operations:

• INSERTED: Contains copies of new rows being added via INSERT statements.
• DELETED: Contains copies of old rows being modified or deleted via UPDATE or DELETE statements.

Step-by-Step Example

Let's create a scenario to demonstrate the usage of Magic Tables with a simple table and a trigger.

Step 1: Creating a Sample Table

First, let’s create a sample table named Employees where we will track employee details:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2)
);

Step 2: Adding a Trigger to Track Changes

Now, we'll add a trigger named trgAfterUpdateEmployee to log changes made to the Employees table using the Magic Tables:

CREATE TRIGGER trgAfterUpdateEmployee
ON Employees
AFTER UPDATE
AS
BEGIN
    -- Displaying the affected rows using Magic Tables
    SELECT 'Old Values' AS ChangeType, * FROM DELETED
    UNION ALL
    SELECT 'New Values' AS ChangeType, * FROM INSERTED
END;

Step 3: Performing DML Operations

Let's insert some initial data into the Employees table and then update a record to see the trigger in action:

-- Insert initial data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'John', 'Doe', 'IT', 50000.00),
       (2, 'Jane', 'Smith', 'HR', 60000.00);

-- Update an employee record
UPDATE Employees
SET Department = 'Finance', Salary = 55000.00
WHERE EmployeeID = 1;

Step 4: Observing Trigger Output

After performing the UPDATE operation, the trigger trgAfterUpdateEmployee will execute and display the contents of the DELETED and INSERTED Magic Tables:

Conclusion

In this article, we explored the concept of Magic Tables in SQL Server, which are powerful tools for tracking and analyzing data changes within triggers. By using INSERTED and DELETED tables, developers can efficiently manage and audit database modifications, ensuring data integrity and consistency across applications.

Understanding how Magic Tables work provides SQL Server developers with valuable insights into data manipulation operations, enabling them to implement robust solutions for handling complex business requirements effectively.

By mastering these concepts, SQL Server professionals can leverage Magic Tables to enhance database performance, audit trails, and data analysis capabilities, thereby contributing to the overall success of database-driven applications.

This article serves as a foundational guide for anyone looking to deepen their understanding of SQL Server triggers and the use of Magic Tables in database development.