Magic Tables in SQL: Complete Guide with Examples

If you're working with SQL triggers, you may have come across the term Magic Tables in SQL. While "magic table" is not an official SQL standard, it is a commonly used term in Microsoft SQL Server to describe special temporary tables that are automatically created during trigger execution.

Understanding magic tables is essential for database developers, DBAs, and anyone working with SQL Server triggers. In this article, we'll explain what magic tables are, how they work, and provide practical examples to help you understand their importance.

What Is a Magic Table in SQL?

Magic tables are a special temporary tables automatically generated by SQL Server when a trigger is executed. These tables store information about the affected rows during INSERT, UPDATE, and DELETE operations.

SQL Server provides two magic tables:

1. inserted – Contains the new version of rows.
2. deleted – Contains the old version of rows.

These tables exist only during trigger execution and are automatically managed by SQL Server.

Why Are They Called Magic Tables?

They are called "magic" because developers do not create, populate, or manage them manually. SQL Server automatically creates and fills these tables whenever a trigger is fired.

This automatic behavior makes them seem almost magical, hence the name "magic tables."

Types of Magic Tables in SQL Server

1. Inserted Table
   The inserted table stores the newly inserted rows or the updated version of rows after an UPDATE operation.
   
   Example

   
   CREATE TRIGGER trigger_name
   ON table_name
   AFTER INSERT
   AS
   	BEGIN SELECT * FROM inserted;
   END;
   

   When a new employee record is inserted, the trigger can access the inserted row through the inserted table.
   
   
2. Deleted Table
   The deleted table stores rows that have been deleted or the original version of rows before an UPDATE operation.
   
   Example

   
   CREATE TRIGGER trigger_name
   ON table_name
   AFTER DELETE
   AS
   	BEGIN SELECT * FROM deleted;
   END;
   

   Whenever a record is deleted, the trigger can access the deleted data using the deleted table.

How Magic Tables Work with Different Operations

SQL Operation	Inserted Table	Deleted Table
INSERT	Contains new rows	Empty
DELETE	Empty	Contains deleted rows
UPDATE	Contains updated rows	Contains original rows

This behavior allows developers to compare old and new values during data modifications.

Practical Example: Tracking Salary Changes

One of the most common use cases of magic tables is auditing data changes.

CREATE TRIGGER trg_SalaryAudit
ON Employees
AFTER UPDATE
AS
BEGIN
	INSERT INTO SalaryAudit ( EmployeeID, OldSalary, NewSalary, ChangeDate )
	SELECT
        d.EmployeeID,
        d.Salary,
        i.Salary,
        GETDATE()
	FROM deleted d
    INNER JOIN inserted i ON d.EmployeeID = i.EmployeeID
    WHERE d.Salary <> i.Salary;
END;

How It Works

• The deleted table contains the employee's old salary.
• The inserted table contains the new salary.
• The trigger stores both values in an audit table.

This helps organizations maintain a history of salary changes.

Benefits of Using Magic Tables

1. Simplified Data Auditing: Magic tables make it easy to track changes to database records.
2. Data Validation: Triggers can validate incoming data before committing changes.
3. Change Tracking: Developers can monitor updates and maintain historical records.
4. Business Rule Enforcement: Organizations can enforce business rules automatically during data modifications.

Important Considerations

When using magic tables, keep the following points in mind:

• They exist only during trigger execution.
• They can contain multiple rows, not just one row.
• Avoid assuming a trigger processes only a single record.
• Always write trigger code that handles multiple rows efficiently.
• Excessive trigger logic can impact database performance.

Best Practices for Using Magic Tables

• Keep trigger logic simple.
• Avoid complex calculations inside triggers.
• Handle multiple-row operations properly.
• Use indexing and optimization techniques when auditing large tables.
• Test trigger performance regularly.

Summary

Magic tables in SQL Server are powerful tools for handling data changes inside triggers. The inserted and deleted tables allow developers to compare old and new values, implement auditing systems, enforce business rules, and track modifications efficiently.

By understanding how magic tables work and following best practices, you can write more reliable and maintainable database triggers while ensuring optimal performance in your SQL Server applications.

Thanks