What is Magic Tables in SQL

Updated on: March 22, 2023

Magic Tables are invisible tables or virtual tables which are getting used in Triggers in SQL SERVER. There are 2 types of Magic Tables one is INSERTED table and another is DELETED table, these tables we can use in triggers of SQL SERVER tables. We will see in detail how we can use these Magic tables to track DML operations on a table, first we will go with creation of Tables: 

Step 1: Create an Employee Table : Use the below CREATE TABLE script to create a new Employee Table:

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [EmpName] [nvarchar](200) NOT NULL,
    [EmpDesignation] [nvarchar](200) NOT NULL,
    [EmpDepartment] [nvarchar](200) NOT NULL,
    [ManagerId] [int] NULL,
    [Salary] INT
)

GO

Step 2: Create an Employee History Table : Use the below CREATE TABLE script to create a new Employee History Table to track entire transactions performed on the Employee Table:

CREATE TABLE [dbo].[EmployeeHistory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [EmpName] [nvarchar](200) NOT NULL,
    [EmpDesignation] [nvarchar](200) NOT NULL,
    [EmpDepartment] [nvarchar](200) NOT NULL,
    [ManagerId] [int] NULL,
    [Salary] [int] NULL,
    [OperationType] [nvarchar](50) NOT NULL,
    [OperationDate] [DateTime] NOT NULL,
)
GO

Step 3: Now, we will create Triggers on Employee Table:

3.1 Insert Trigger

CREATE TRIGGER TR_Employee_Insert ON Employee
AFTER INSERT
AS
BEGIN
INSERT INTO EmployeeHistory(EmployeeId, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, OperationType, OperationDate)
SELECT Id, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, 'Add', GETDATE() FROM INSERTED
END

3.2: Delete Trigger:

CREATE TRIGGER TR_Employee_Delete ON Employee
AFTER DELETE
AS
BEGIN
INSERT INTO EmployeeHistory(EmployeeId, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, OperationType, OperationDate)
SELECT Id, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, 'Delete', GETDATE() FROM DELETED
END

3.3 Update Trigger:

CREATE TRIGGER TR_Employee_Update ON Employee
AFTER UPDATE
AS
BEGIN

INSERT INTO EmployeeHistory(EmployeeId, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, OperationType, OperationDate)
SELECT Id, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, 'Delete', GETDATE() FROM DELETED

INSERT INTO EmployeeHistory(EmployeeId, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, OperationType, OperationDate)
SELECT Id, EmpName, EmpDesignation, EmpDepartment, ManagerId, Salary, 'Add', GETDATE() FROM INSERTED

END