Exploring Magic Tables in SQL Server: A Powerful Tool for Data Modification and Triggers

 Magic Tables are special tables in SQL Server that are automatically created and populated during certain types of operations, such as data modification statements (e.g., INSERT, UPDATE, DELETE) or triggers. These tables provide easy access to the modified data within the scope of the operation or trigger.

There are two types of Magic Tables in SQL Server:

1. Inserted table: This table is available during INSERT and UPDATE operations or triggers. It contains the rows that are being inserted or updated into the target table. You can reference the columns of the inserted table to access the new values being inserted or updated.

2. Deleted table: This table is available during UPDATE and DELETE operations or triggers. It contains the rows that are being updated or deleted from the target table. You can reference the columns of the deleted table to access the old values before the modification.

The structure of the inserted and deleted tables mirrors the structure of the target table on which the operation or trigger is being performed. They contain the same columns with the same data types.

Here's an example of how you can use the inserted and deleted tables in an UPDATE trigger:

CREATE TRIGGER MyTrigger ON MyTable
AFTER UPDATE
AS
BEGIN
    -- Accessing the new values using the inserted table
    SELECT * FROM inserted;

    -- Accessing the old values using the deleted table
    SELECT * FROM deleted;
END

In the above example, the trigger is fired after an UPDATE operation on the MyTable table. Inside the trigger, you can reference the inserted table to access the new values being updated and the deleted table to access the old values before the update.

Magic Tables provide a convenient way to access the affected data within triggers and perform additional logic or auditing based on the changes made to the tables.