Magic Tables Used by Triggers in SQL Server 2012

In this article I have described the magic tables and how to use magic tables in SQL Server.

Introduction

Usually, inserted and deleted tables are called Magic Tables in the context of a trigger. There are Inserted and Deleted magic tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert, Update and Delete) on a database table.

Use of magic tables

Basically, magic tables are used by triggers for the following purposes.

The following is an example of creating a table:

CREATE TABLE emp(
empId int NOT
NULL,
emp_Name varchar(15)
NULL,
emp_sal int
NULL

)

insert
into emp 
values
(1, 'ram',10000)
insert
into emp
values
(2, 'ramesh',11000)
insert
into emp
values
(3, 'manish',12000)
 

SELECT
  *   FROM emp

table-in-sql.jpg

Inserted magic table

The Inserted table holds the recently inserted values, in other words new data values. Hence recently added records are inserted into the Inserted table.

Create inserted trigger

The following is an example of creating an inserted trigger on a table:

CREATE TRIGGER trg_Emp_ins
ON
emp
FOR
INSERT
AS
begin
SELECT
* FROM INSERTED -- show data in Inserted logical table
SELECT
* FROM DELETED -- show data in Deleted logical tableend
End

Output

tables-in-sql.jpg

Now insert records into the Emp table to see the data within the Inserted and Deleted magic table.

insert into emp (empId,emp_Name,emp_sal) values (4, 'sita',14000)
select
* from emp

Output

magic-table-in-sql.jpg

Deleted magic table

The Deleted table holds the recently deleted or updated values, in other words old data values. Hence the old updated and deleted records are inserted into the Deleted table.

Create deleted trigger

The following is an example of creating a deleted trigger on a table:
 
CREATE TRIGGER trg_Emp_Upd
ON
emp
FOR
UPDATE
AS
begin
SELECT
* FROM INSERTED -- show data in INSERTED logical table
SELECT
* FROM DELETED -- show data in DELETED logical table
End

Output

see-magic-table-in-sql.jpg

Now update the record in the Emp table to see the data in the Inserted and Deleted magic table.

update  emp
set
emp_sal =13000
where
empId=
select
* from emp

Output

show-magic-table-in-sql.jpg