Magic table in sql server

 Magic table in sql server

In SQL Server, there is no specific concept called a "magic table." However, the term "magic table" is commonly used to refer to the special tables that are used in triggers to access the data that was modified by the triggering action (such as an INSERT, UPDATE, DELETE statement). These special tables are known as inserted and deleted tables.

Here's how they work:

1. **Inserted Table:**

   - In the context of an `INSERT` or `UPDATE` trigger, the `inserted` table contains the new rows that were inserted or the updated rows after the modification.

   - For example, in an `INSERT` trigger, you can access the newly inserted rows from the `inserted` table to perform further actions based on the inserted data.

   ```sql

   CREATE TRIGGER trgAfterInsert

   ON TableName

   AFTER INSERT

   AS

   BEGIN

       -- Inserted table contains newly inserted rows

       SELECT * FROM inserted;

   END

   ```

2. **Deleted Table:**

   - In the context of a `DELETE` or `UPDATE` trigger, the `deleted` table contains the old rows that were deleted or the rows before they were updated.

   - For example, in a `DELETE` trigger, you can access the deleted rows from the `deleted` table to perform actions before the rows are deleted permanently.

   ```sql

   CREATE TRIGGER trgAfterDelete

   ON TableName

   AFTER DELETE

   AS

   BEGIN

       -- Deleted table contains rows that were deleted

       SELECT * FROM deleted;

   END

   ```

By using these `inserted` and `deleted` tables, you can effectively access the data being modified by the triggering SQL statement within the body of the trigger. These tables are sometimes colloquially referred to as "magic tables" due to their special and implicit nature within triggers.