Magic table in SQL

The magic table in SQL is a powerful feature that allows you to access the before and after values of rows affected by (INSERT, UPDATE, or DELETE) operations within triggers. These invisible tables are known as 

1. INSERTED 
2. DELETED

They simplify doing more or thinking more complexly dependent on table modifications. This article will delve into the concept of magic tables, their usage in triggers, and their significance in SQL Server.

What is a magic table in SQL?

As the name suggests, the magic table in SQL possesses a certain mystique in SQL Server. They are temporary, virtual tables that the database engine internally manages. Magic tables are accessible exclusively within triggers and store the affected row values during DML operations. Two main types of magic tables exist: INSERTED and DELETED.

An interesting questions for the SQL enthusiasts:-

Which SQL Keyword Is Used To Retrieve A Maximum Value?

INSERTED Table

The INSERTED table holds the new row values affected by an INSERT or UPDATE operation. The INSERTED table contains the newly inserted rows when an INSERT statement is executed. 

In the case of an UPDATE statement, the INSERTED table holds the updated values of the rows. This table enables triggers to access and analyze the latest data.

DELETED Table

The DELETED table, on the other hand, stores the before version of rows affected by an UPDATE or DELETE operation. During an UPDATE, the DELETED table holds the original values of the modified rows. In the case of a DELETE statement, the DELETED table contains the deleted rows. It allows triggers to access the previous state of the data and perform comparisons or logging operations. 

Working of magic tables in SQL server

Triggers are database objects that automatically execute in response to specified events. Magic tables play a crucial role within triggers, providing contextual information to perform various actions. Let’s consider an example trigger to illustrate the usage of magic tables:

In this scenario, the trigger named “myTrigger” is defined on the “myTable” table and executes after an INSERT operation occurs. The SELECT statement within the trigger retrieves all the inserted rows from the INSERTED table. This example demonstrates a simple usage of a magic table to access the affected data.

What are Benefits of Magic Table in SQL

Magic Table in SQL offer several benefits and applications within SQL Server:

1. Auditing: By accessing the before and after values of rows, magic tables enable the implementation of comprehensive auditing mechanisms to track changes made to the data.
2. Complex Logic: The values stored in magic tables can be leveraged to implement complex business logic or perform conditional operations within triggers.
3. Data Validation: Magic tables allow triggers to validate data modifications based on the old and new values, ensuring data integrity and adherence to business rules.
4. Logging and Archiving: Triggers utilizing magic tables can log and archive data changes for historical purposes or compliance requirements.

Examples of Magic Table in SQL

Let’s see some examples of the magic table in SQL.

Example 1

Consider the following code –

This trigger will fire after any INSERT operation on the “myTable” table. The SELECT * FROM INSERTED; statement will then select all the rows inserted into the “myTable” table.

The INSERTED table is a magic table created and managed internally by SQL Server. It stores the before version of the row for any INSERT, UPDATE, or DELETE operations. In this case, the INSERTED table will store the data of the row just inserted into the “myTable” table.

We can use the INSERTED table to audit data changes in the “myTable” table. For example, we could use the INSERTED table to track who inserted a row, when, and the data before it was inserted.

We can also use the INSERTED table to manage data in the “myTable” table. For example, we could use the INSERTED table to bulk update the “myTable” table.

Example 2

I’ll break it up into small pieces for this example. So we can see how the code should be put together. We will understand it block by block. You will also get an idea of how we should think while coding.

Explanation: This snippet creates a trigger named “SalaryChangeTrigger” that fires after an update operation occurs on the “Employees” table. The trigger’s code block begins with the BEGIN statement.

Explanation: This condition checks if the “Salary” column is being updated. It ensures that the trigger only fires when the salary is modified.

Explanation:- These lines declare variables @EmployeeID, @OldSalary, and @NewSalary to store the employee ID, old salary, and new salary values.

Explanation: This statement retrieves the employee ID and old salary from the DELETED magic table. The DELETED table holds the values of the rows before the update.

Explanation: Here, we retrieve the new salary from the INSERTED magic table using the employee ID obtained from the DELETED table. The INSERTED table holds the updated values of the rows.

Explanation: This line inserts a new row into the “SalaryChangeLog” table with the captured employee ID, old salary, new salary, and the current date and time retrieved using GETDATE().

Explanation: The code block of the trigger ends with the END statement, closing the trigger definition.

By breaking down the code into these snippets, we can understand each part’s purpose and how they contribute to the functionality of the trigger.

Full code

The complete code will look like this.

In this example, when a salary update occurs in the “Employees” table, the trigger fires and captures the employee ID, old salary, new salary, and the change date into the “SalaryChangeLog” table.

By utilizing both the INSERTED and DELETED magic tables within the trigger, we can compare the old and new values of the salary column and log the changes accordingly.

Conclusion

Magic tables give SQL Server triggers a powerful set of tools for working with changes to rows. INSERTED & DELETED tables let you see the old and new values of rows that have been changed by INSERT, UPDATE, or DELETE actions. This lets you use a number of tools. 

If you understand and use magic tables well in your SQL Server setting, you can improve data management, audits, and the way you use complicated logic.