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.
