The Magic Behind The Magic Tables in SQL Server

Imagine attending magic party shows at your school's annual function. You always thought did the magician pull up a handkerchief from thin air or how could he make the entire bowl of sweets disappear before your eyes? As you grew up, you learned that all those tricks you saw in your school had science behind them. The handkerchief is stored in some hidden chamber inside a magician's robe, or the sweets disappear under the hidden box. We will learn different aspects of the magic table; their uses in SQL Server. SQL Server also has a similar feature that allows users to manipulate inserted, updated, or deleted data using cursors.

What are Triggers?

Triggers are the SQL codes automatically executed in response to certain events on a table. A trigger in SQL works like a real-world trigger. How is this related to Triggers in SQL? Because a trigger resides in the database and anyone with the required privilege can use it, it lets you write a set of SQL statements that multiple applications can use. It lets you avoid redundant code when multiple programs need to perform the same database operation.

SQL Server has three types of triggers: 

1. DML (Data Manipulation Language) Triggers. 
2. DDL (Data Definition Language) Triggers.
3. Logon Triggers.

 What is The Use of  Triggers?

Triggers allow you to perform the following tasks: Enforce business rules. Validate input data. Generate a unique value for a newly inserted row on a different file. One of the practical uses of triggers is in Audit Trail.

Where are Triggers Located in a Database

Unlike other objects like views etc., which have a separate tab, triggers are located under a particular table.

What is Audit Trail?

Auditing reviews and analyzes management, operational, and technical controls. Auditing reviews and analyzes management, operational, and technical controls. An audit trail is a series of records of computer events about an operating system, an application, or user activities. A computer system may have several audit trails, each devoted to a particular type of activity. SQL Server handles Audit Trail using Magic Tables.

 What are Magic Tables

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.

There are two types of magic tables

1. Inserted
2. Deleted

Comparison between Inserted and Deleted Magic Tables

 SQL Server also uses magic tables outside the TRIGGER for many other purposes. A magic table can be utilized in INSERT, UPDATE, and DELETE activities with the table in a trigger, which is the common understanding of people. Using Magic tables in SQL Server with the usual update statement lessens the information dependency and makes your information consistent with your transaction. 

Everyday Use of Magic Tables

An everyday 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. Users can write data manipulation logic 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 into the picture. Whenever the magic table is utilized with a query statement in the transaction, tempdb will be affected by that statement. Below are the limitations of the magic table compared to the actual temp table (# table).

• Users can not create any index or apply any constraint on the magic tables in SQL Server.
• They cannot be altered because the purpose of the magic table is to audit the information in the system.

How to Use Magic Tables in Triggers

If you have implemented a trigger for a table, then:

1. Whenever you Insert a record on that table, that record will be shown in the INSERTED Magic Table.
2. Whenever you Delete the record on that table, that record will be shown in the DELETED Magic Table Only. 
3. Whenever you Update the record on that table, that existing record will be shown in the DELETED Magic Table. Updated new data will be shown in the INSERTED Magic Table.

 Now let us understand how magic tables work in real life. For that, we have created a table called test user as below.

Create a table test user.

 
(
user_id  int,
FirstName varchar(100),
LastName varchar(100),
Address varchar(100)
)

 The output looks like the one below.

Inserted Virtual Table

Suppose we have a test user table, as shown in the above figure. The Inserted table holds the recently inserted values, which will be shown in INSERTED Magic Table. We need to create a trigger to see data within Inserted virtual tables. We are creating a trigger to see data in the Inserted virtual table.

Create TRIGGER  Trigger_ForInsertmagic 
ON test user
FOR INSERT 
AS 
begin 
SELECT * FROM INSERTED 
end

Now let us insert a record in the table test user.

insert into test user values(12, 'Rahul,' 'Sharma', 'Bombay') 

SELECT * FROM test user

The output looks like the one below.

The top section is the output of inserted magic table, and the bottom section is the output of the test user table with the new record inserted.

Deleted Virtual Table

The Deleted table holds the recently deleted values. Hence that record will be shown in the DELETED Magic Table. Suppose we have a test user table, as shown in the above figure. We must create a trigger to see the data in the deleted virtual tables. To create a trigger to see the data in the deleted virtual table, use the following:

Create TRIGGER  Trigger_Fordeletemagic 
ON test user
FOR DELETE 
AS 
begin 
SELECT * FROM Deleted 
end 

Now let us delete a record from the test user table:

Delete from test user where User_id=12. 

SELECT * FROM test user

The output looks like the one below.

The first section is the output of the deleted magic table, and the second section shows that the record has been deleted.

Update The Record in Table

To update the record in the test user table, we use it for both virtual tables. One shows the inserted table, and the other shows the deleted table. The following trigger defines both the inserted table and the deleted table:

Create TRIGGER  Trigger_ForInsertdeletemagic 
ON test user
FOR UPDATE 
AS 
begin 
SELECT * FROM INSERTED 
SELECT * FROM DELETED 
end 

Now let us update a record with the following query

Update test user set firstname='ABC'  where User_id=12 

SELECT * FROM test user

The output is as below

The first row shows the output of the magic table, and the second row shows that the data has been updated in the test user table.

Use of Magic Tables in Non-Trigger Activities

You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions. Below is the code snippet

IF OBJECT_ID('tempdb..#ChangeTracking', 'U') IS NULL

IF OBJECT_ID('tempdb..#ChangeTracking', 'U') IS NULL BEGIN   -- DROP TABLE #ChangeTracking

CREATE TABLE #ChangeTracking (
ChangeDT DATETIME NOT NULL DEFAULT (GETDATE()),
oID INT NULL,
nID INT NULL,
oCol_1 VARCHAR(10) NULL,
nCol_1 VARCHAR(10) NULL,
oCol_2 VARCHAR(10) NULL,
nCol_2 VARCHAR(10) NULL,
oCol_3 VARCHAR(10) NULL,
nCol_3 VARCHAR(10) NULL
);
END;

IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL
BEGIN   -- DROP TABLE #TestData
CREATE TABLE #TestData (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Col_1 VARCHAR(10) NULL,
Col_2 VARCHAR(10) NULL,
Col_3 VARCHAR(10) NULL
);
END

--=================================================================

INSERT #TestData (Col_1, Col_2, Col_3)
OUTPUT Inserted.* INTO #ChangeTracking (nID, nCol_1, nCol_2, nCol_3)
SELECT 'abc,' 'def,' 'ghi' UNION ALL
SELECT 'jkl,' 'mno,' '123' UNION ALL
SELECT '346', '789', 'qaz' UNION ALL
SELECT 'wsx,' 'edc,' 'of' UNION ALL
SELECT 'tgb,' 'yhn,' 'ujm' UNION ALL
SELECT 'plm,' 'uhb,' 'tfc';
-- see what's been captured so far...
SELECT * FROM #TestData td;
SELECT * FROM #ChangeTracking ct;

------------------------------------------------

UPDATE td SET
td.Col_1 = 'xxx',
td.Col_3 = 'zzz'
OUTPUT Deleted.*, Inserted.*
INTO #ChangeTracking (oID, oCol_1, oCol_2, oCol_3, nID, nCol_1, nCol_2, nCol_3)
FROM
#TestData td
WHERE
td.ID IN (2, 4, 6);
-- see what's been captured so far...
SELECT * FROM #TestData td;
SELECT * FROM #ChangeTracking ct;

------------------------------------------------

DELETE td
OUTPUT Deleted.* INTO #ChangeTracking ( oID, oCol_1, oCol_2, oCol_3)
FROM
#TestData td;
-- see what's been captured so far...
SELECT * FROM #TestData td;
SELECT * FROM #ChangeTracking ct;

The output looks like the one below.

Conclusion

Over the last few paragraphs, we have discussed the different aspects of magic tables, their use, and their advantage. There is a notion that magic tables are only associated with triggers. We learned that apart from triggers, it can also be used in non-trigger activities using the output clause. This can be a good learning experience for users, encouraging them to learn further about magic tables.

SQL Testing Training

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now