Post Details

Magic table in SQL | What is magic table in SQL
Technical
np

Mon , Dec 04 2023

np

Magic table in SQL


A magic table in SQL is a temporary logical table that is created by the SQL server whenever there are insertion, deletion, or update operations on a table.

These tables store the before and after values of the affected rows and can be accessed only within triggers.

There are two types of magic tables: INSERTED and DELETED.

The INSERTED table holds the new values of the rows that are inserted or updated, while the DELETED table holds the old values of the rows that are deleted or updated.

Magic tables can be used for auditing, data manipulation, or enforcing business rules.

They are not physical tables and they cannot be altered, indexed, or constrained.

They are stored in the tempdb database and are automatically dropped after the trigger execution.


Lets take an example-

CREATE TABLE students
( 
 stu_id varchar(10),
 stu_name varchar(20),
 branch varchar(20)
);

Now insert few values-

INSERT INTO students VALUES
('1901401','Nil','C.S'),
('1901402','HARISH','C.S'),
('1901403','ABHI','C.S'),
('1901404','TIM','C.S'),
('1901405','SAM','C.S');

Now select what we have inserted-

SELECT * FROM students;

Creating a trigger T1 on insert operation :

CREATE TRIGGER T1 ON students
AFTER INSERT
AS
BEGIN
SELECT * FROM INSERTED
END
Now inserting and selecting the values will show you the difference-
INSERT INTO students VALUES
('1901406','PRADEEP','C.S'),
('1901407','DEVESH','C.S');
SELECT* FROM students ;


As above mentioned example we can also create trigger on Update operation and we can see how we can get values of magic table inserted and deleted. lets see an example-

CREATE TRIGGER T2 ON students
AFTER DELETE
AS
BEGIN
SELECT * FROM DELETED
END
UPDATE students SET stu_name= 'VANSH' 
WHERE stu_id = '1901401'
SELECT* FROM students

#tags:

#magic table in sql

#how to create magic table in sql server

#types of magic table in sql server

#magic table in sql server with example

#magic table in sql w3schools

#magic table in sql server 2012 with example

#magic table in stored procedure sql server

#magic table in sql with example

#how to use magic table in sql server

#magic table in sql server

#magic tables in sql

#magic tables sql server

#sql magic table

#the magic table


!! Do Share  !!

Hope you found this article helpful.

Thanks for your time, keep reading happy reading at lifeDB.in, please do follow, like comment.


     Instagram Page :Follow lifedb_official on insta

     Facebook Profile:Follow Lifedb_official page on facebook

  Linkedin Profile:Follow lifedb_official on linkdin

      Twitter Profile: Follow lifedb_official on X


Please log in to like or dislike this post.

Previous Next

Leave a Reply

Please log in to Comment On this post.

np

np

I am an engineer with over 10 years of experience, passionate about using my knowledge and skills to make a difference in the world. By writing on LifeDB, I aim to share my thoughts, ideas, and insights to inspire positive change and contribute to society. I believe that even small ideas can create big impacts, and I am committed to giving back to the community in every way I can.

Related Post