Cyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
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.
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:
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.
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.
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
Comparison between Inserted and Deleted Magic Tables
Inserted |
Deleted |
An INSERTED magic table is populated with INSERT and UPDATE operations |
The DELETED magic table is populated with UPDATE and DELETE operations. |
The INSERTED magic table stores the before version of the row |
DELETED table stores the after version of the row for any INSERT, UPDATE, or DELETE operations. |
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.
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).
If you have implemented a trigger for a table, then:
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.
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.
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.
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.
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.
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
What is Schema in SQL With Example: All You Need to Know
Data Definition Language (DDL) Commands in SQL
How To Create Database Table-All You Need To know
What does a Database Administrator do? A Detailed Study
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment