what-are-inserted-and-deleted-tables-or-magic-table-in-sql

In SQL Server, a magic table is not an actual table in the database, but a term often used to refer to the inserted and deleted tables that are used within triggers. These tables temporarily hold the data that is being inserted, updated, or deleted in the target table, allowing the trigger to perform actions based on the changes made to the data.

What are Inserted and Deleted Tables?

Inserted table: This table holds the new rows of data that are being inserted or updated in the table. If a row is being inserted, it will only appear in the inserted table. If a row is being updated, the new version of the row will appear in the inserted table.
Deleted table: This table holds the rows that are being deleted or updated in the table. If a row is being deleted, it will only appear in the deleted table. If a row is being updated, the old version of the row will appear in the deleted table.

These "magic tables" allow triggers to compare the old and new data to perform any necessary actions.

How Does a Trigger Use Inserted and Deleted Tables?

Here’s an example that demonstrates how the inserted and deleted tables are used in a trigger. Let’s assume we have a table called Employees.

Example: `Employees` Table

    CREATE TABLE Employees (
        EmployeeID INT PRIMARY KEY,
        FirstName NVARCHAR(100),
        LastName NVARCHAR(100),
        Department NVARCHAR(100)
    );

Example Trigger: Using Inserted and Deleted Tables

    CREATE TRIGGER trg_EmployeeUpdate
    ON Employees
    FOR UPDATE, DELETE
    AS
    BEGIN
        -- Check what was updated (in Inserted table)
        IF EXISTS (SELECT * FROM inserted)
        BEGIN
            PRINT 'Updated Employee Data:'
            SELECT * FROM inserted;  -- New data after update
        END

        -- Check what was deleted (in Deleted table)
        IF EXISTS (SELECT * FROM deleted)
        BEGIN
            PRINT 'Deleted Employee Data:'
            SELECT * FROM deleted;  -- Old data before deletion
        END
    END;

Explanation:

FOR UPDATE, DELETE: This specifies that the trigger will fire on both UPDATE and DELETE operations.
Inserted table: Contains the new data (after update or insert).
Deleted table: Contains the old data (before update or before delete).

When the trigger is executed, it will display the old values (from the deleted table) and the new values (from the inserted table). You can use this data to perform additional logic, such as logging changes, enforcing business rules, or cascading changes to other tables.

Example Execution:

Let’s say we update an employee’s department:

    UPDATE Employees
    SET Department = 'Marketing'
    WHERE EmployeeID = 1;

Output from the trigger might be:

    Updated Employee Data:
    EmployeeID  FirstName   LastName   Department
    1           John        Doe        Marketing

    Deleted Employee Data:
    EmployeeID  FirstName   LastName   Department
    1           John        Doe        Sales

In this case, the inserted table shows the updated department (Marketing), and the deleted table shows the original department (Sales).

Use Cases for Inserted and Deleted Tables:

Tracking Changes: You can use the inserted and deleted tables to track changes made to a table (e.g., changes in employee details, product prices).
Auditing: These tables are useful for auditing purposes. You can log changes to another table or store the old and new values for later analysis.
Cascading Updates: Sometimes, when an update occurs, you may want to cascade the changes to other tables. For example, updating an employee's department may require updating records in related tables.

Summary

Inserted and Deleted tables are "magic tables" used in SQL Server triggers.
Inserted holds the new data after an insert or update.
Deleted holds the old data before a delete or update.
These tables provide a way to react to changes in the data, allowing for powerful and flexible actions, such as auditing, logging, or cascading changes.

If you want to perform more complex operations on the data during a trigger, you can reference these "magic tables" to compare old and new data, or perform other actions as needed.