There are Magic Tables (virtual tables) in SQL Server that hold the temporal information of recently inserted and recently deleted data in the virtual table. INSERTED and DELETED are two types of magic tables in SQL Server. An INSERTED magic table is populated with INSERT and UPDATE operations and DELETED magic table is populated with UPDATE and DELETE operations.
The INSERTED magic table stores the before version of the row, and the DELETED table stores the after version of the row for any INSERT, UPDATE, or DELETE operations.
A magic table can be utilized in INSERT, UPDATE, and DELETE activity with the table in a trigger, which is the common understanding of people. SQL Server uses magic tables outside the TRIGGER also for a lot of other purposes too. Use of Magic tables in SQL Server with the usual update statement lessens the information dependency and makes your information consistent with your transaction.
INSERT: The INSERTED magic table will have the newly inserted rows on the top in the table with an insert operation. It can be used to manage an audit of the table to another history table.
DELETE: The DELETED magic table will have the recently deleted rows on the top in the table with a delete operation. It can be used to manage a previous version of the row for the audit purpose in the history table.
UPDATE: Both INSERTED and DELETED virtual tables will be part of an update statement. Update statement returns deleted magic table with the previous version of the row and the inserted magic table with the new version of a row, which is going to be replaced or updated with the earlier values in the table. The important thing is that whenever users perform the update statement inside the trigger or outside the trigger, INSERTED and DELETED magic tables are being used.
A common use of Magic tables in SQL Server is the DML (Data Manipulation Language) trigger. SQL Server DML trigger allows using these two virtual tables INSERTED and DELETED. The ideal use of the trigger is auditing and managing a before and after version of the table row on INSERT, UPDATE, or DELETE operation within the transaction statement. Even users can write data manipulation logic as well with these magic tables inside the trigger.
A magic table is stored in the temp DB. Therefore, whenever you use the magic tables in SQL Server with the query statement, tempdb will come in the picture.
Whenever the magic table is utilizing with a query statement in the transaction, tempdb will be affected by that statement. Below are the limitations of the magic table compare to the actual temp table (# table)
- Users can not create any index or apply any constraint on the magic tables in SQL Server
- They can not be altered because the purpose of the magic table is to audit the information in the system
How to use Magic Tables in the Trigger?
Data manipulation with the magic table is much useful in the trigger for information auditing. Users can use join operations with the magic tables in SQL Server and compose a business logic as well. For example, a trigger to check if an update operation is performed on any particular column or not? If it happened, then execute the required statements to perform such tasks.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] AFTER UPDATE AS BEGIN SET NOCOUNT ON; --IF UPDATE([ProductID]) OR UPDATE([OrderQty]) --BEGIN INSERT INTO [Production].[TransactionHistory](ProductID, ReferenceOrderID, TransactionType, TransactionDate, Quantity) SELECT i.ProductID, i.WorkOrderID, 'W', GETDATE(), i.OrderQty FROM inserted AS i; INSERT INTO [Production].[TransactionHistory](ProductID, ReferenceOrderID, TransactionType, TransactionDate, Quantity) SELECT d.[ProductID], d.[WorkOrderID], 'W', GETDATE(), d.[OrderQty] FROM deleted AS d; --END; END; |
Here, uWorkOrder trigger exists inside the WorkOrder table in the Microsoft practice database (AdventureWorks). Users can write triggers with condition-based logic with the help of the UPDATE() function. UPDATE() function refers to that update operation is performed or not on the particular column, which is being used in the UPDATE() function inside the trigger. Inserted and deleted are the magic tables in the SQL Server trigger that used to manage pre-updated and post updated row.
The virtual table (Magic Table) will be removed from the tempdb once a transaction gets finished. In the above sample code, we are inserting both row versions (before and current) into another audit table.
Update Table with returning an updated row using Magic Tables in SQL Server
A magic table can be accessed outside the DML trigger as well. Most of the developers don’t know about this utilization of a magic table with an update statement. For example, when a user wants to return updated rows with the update statement. A developer will first update a row and return those rows with applying a filter, whatever filters are applied earlier with the update statement on the table. However, it could return dirty rows, because that rows can be updated by another transaction as well meanwhile.
In the second case, users fetch and store that updatable rows into the temp table by applying a required filter on a table, perform an update operation and return those rows from the temp table in the same transaction. But inserting those rows into the temp table and before completing the update statement, meanwhile another transaction can carry those rows for the same activity which are going to be updated. To overcome this problem statement and challenges, the user can utilize the magic tables in SQL Server with the update statement, which will return deleted (early version) and inserted (new version) rows with INSERTED and DELETED.
For example, you can see the below sample table, [WorkOrder], with a filter applied to it.
Now, updating a table row and fetching those rows with the use of a magic table and (#) temp table.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE #order(WorkOrderID INT, ProductID INT, OrderQty INT, StockedQty INT) UPDATE [WorkOrder] WITH (ROWLOCK) SET OrderQty = OrderQty - 1 OUTPUT INSERTED.WorkOrderID, INSERTED.ProductID, INSERTED.OrderQty, INSERTED.StockedQty INTO #order WHERE ProductID = 722 AND WorkOrderID = 45 SELECT * FROM #order |
In the above T-SQL query sample, an INSERTED magic table will return the newly inserted value and carrying it to the #order temp table. However, we are not updating the column WorkOrderID, ProductID, and StockedQty in the above query statement, but the inserted virtual table can return other columns of the table as well.
Here, an INSERTED magic table is used to return an updated row. The newly updated value of the column can return in the response with the help of the OUTPUT parameter in the same statement. Even users can return early versions of updated rows as well with the help of the DELETED magic table in the same T-SQL statement.
For example,
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE #order(WorkOrderID INT, ProductID INT, OrderQty INT, perv_OrderQty INT) UPDATE [WorkOrder] WITH (ROWLOCK) SET OrderQty = OrderQty - 1 OUTPUT INSERTED.WorkOrderID, INSERTED.ProductID, INSERTED.OrderQty, DELETED.OrderQty as prev_OrderQty INTO #order WHERE ProductID = 722 AND WorkOrderID = 45 SELECT * FROM #order |
Here, a newly updated value is 29, which is inserted into the temp table using an INSERTED magic table and column alias with prev_OrderQty that returns the before update version of a row with the help of a DELETED magic table.
The most significant scenario is that when a user is updating (n) rows using the TOP (n) keyword with the UPDATE statement, and a user wants those rows in query results, which are updated with the UPDATE statement. We don’t have any appropriate choice to achieve it except the MAGIC tables in SQL Server. If you are using READ UNCOMMITTED ISOLATION level, then both scenarios will return a dirty read. If you are fetching rows with the READ COMMITTED ISOLATION level, then there are chances of blocking to occur with the transaction.
For example,
|
1 2 3 4 5 |
UPDATE TOP (n) TableName WITH(ROWLOCK) SET ColumnD = ? OUTPUT INSERTED.ColumnA, INSERTED.ColumnB, ..... INTO #OutTable WHERE ColumnA = ? |
Here, TOP (n) rows will get updated in the above UPDATE statement, and those updated rows will be inserted into the #OutTable temp table. Here, a magic table with an update statement resolves a lot of data dependencies. TOP (n) with an update statement should be useful when a multi-threading application picks data update activity on the table parallelly. Any of the free workers will pull the (n) records from a table with an update and process those rows at the application side. Even Magic tables help to decrease the code testing time as well in the above-explained scenarios.
Conclusion
The purpose of this article is not only to introduce the magic tables (INSERTED and DELETED) in a trigger, but users can utilize it outside the trigger with a regular update statement as well.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020