Magic Tables In SqlServer

MAGIC TABLES...
------------------------------------------------------------------------------------
SQL Server allows you to define a Magic Table. Magic Tables are invisible tables
 or virtual tables. You can see them only with the help Triggers in SQL Server.
Magic Tables are those tables which allow you to hold inserted, deleted and
updated values during insert, delete and update DML operations on a table in
SQL Server. So let's have a look at a practical example of how to use Magic Tables
 in SQL Server. The example is developed in SQL Server 2012 using the SQL Server
 Management Studio.

 --> These are the two Magic Tables:

     1) Inserted
     2) Deleted

Generally Magic Tables are invisible tables, we can only see them with the help
of Trigger's in SQL Server.

    CREATE TABLE TEST (SNO INT,SNAME VARCHAR(50),SLOC VARCHAR(50))

Insert Below Data First
-----------------------
1) insert into TEST values(1, 'KISH','KURNOOL');
2) insert into TEST values(2, 'SAI','KURNOOL');
3) insert into TEST values(3, 'HARI','KURNOOL');
4) insert into TEST values(4, 'RAVI','WARANAGL');
5) insert into TEST values(5, 'YES','WGL');
6) insert into TEST values(6, 'NO','WG');

select * from test order by sno;

1) Inserted :
-------------
       ALTER TRIGGER  Trigger_ForInsertmagic ON TEST
       FOR INSERT AS
       begin
         SELECT * FROM INSERTED
       end

7) insert into TEST values(7, 'SRINU','PRAKASHAM');
running the above query it will fire the trigger  Trigger_ForInsertmagic and shows below result automatically
7 SRINU PRAKASHAM

--> upon inserting the record in table (TEST) it shows the inserted data automatically ,when we write the above trigger by using magic tables

select * from test order by sno;
--------------------------------
1 KISH KURNOOL
2 SAI KURNOOL
3 HARI KURNOOL
4 RAVI WARANAGL
5 YES WGL
6 NO WG
7 SRINU PRAKASHAM


2) Updated:
-------------
       ALTER TRIGGER  Trigger_ForUpdatemagic ON TEST
       FOR UPDATE AS
       begin
         SELECT * FROM INSERTED
       end

UPDATE TEST SET SNAME='raju' WHERE SNO=3;
running the above query it will fire the trigger  Trigger_ForUpdatemagic and shows below result
3 raju KURNOOL
--> upon Updating the record in table (TEST) it shows the Updated data automatically ,when we write the above trigger by using magic tables

select * from test order by sno;
1 KISH KURNOOL
2 SAI KURNOOL
3 raju KURNOOL
4 RAVI WARANAGL
5 YES WGL
6 NO WG
7 SRINU PRAKASHAM


2) Deleted :
------------
       ALTER TRIGGER  Trigger_ForDeleteMagic ON TEST
       FOR DELETE AS
       begin
         SELECT * FROM DELETED
       end

DELETE FROM TEST WHERE SNO=7;
running the above query it will fire the trigger  Trigger_ForDeleteMagic and shows below result
7 SRINU PRAKASHAM

--> upon Deleting the record in table (TEST) it shows the Deleted data automatically ,when we write the above trigger by using magic tables
     SELECT * FROM TEST;
1 KISH KURNOOL
2 SAI KURNOOL
3 raju KURNOOL
4 RAVI WARANAGL
5 YES WGL
6 NO WG

if we delete not available value , it will show empty result