Magic tables in SQL:
Magic tables are the logical tables in SQL server. There are two
types of logical tables in SQL server:
Magic tables or Logical tables are automatically created
and managed by SQL Server internally. These tables hold the recently inserted,
deleted and updated values during Insert, Update and Delete operations on a
table. These tables are not visible and accessible directly. There
are two methods to access these tables
- Using
Triggers operation either After Trigger or Instead of trigger.
- Without
Triggers Using “OUTPUT” Clause
Using Triggers:
Inserted Logical Table:
Inserted logical table holds the latest inserted value or
updated value in the table.
Whenever we do insertion or updating the record in table in
database, a table gets created automatically by the SQL server, named as
INSERTED.
CREATE TABLE [dbo].[emp_details](
[empid] [numeric](18, 0) NOT NULL,
[empname] [varchar](100) NULL,
[salary] [numeric](18, 0) NULL,)
|
Now Create a trigger
CREATE TRIGGER emp _Insertion
ON emp_details
FOR INSERT
AS
begin
SELECT * FROM
INSERTED
SELECT * FROM
DELETED
End
|
Now Inert data
in above table:
INSERT INTO emp_details (empid, empname, salary) VALUES (201, ’XYZ’ ,1000)
|
Deleted Logical Table:
When we update the record in table then two tables are
created, one is INSERTED and another is called DELETED. Deleted table will hold
the previous record after the updations and Inserted table consists of
the updated record.
CREATE TRIGGER emp_update ON emp_details
FOR update
AS
begin
SELECT * FROM
INSERTED
SELECT * FROM
DELETED
End
|
Update Record
update emp_details set empname='ABC' where empid=201
|
Without Triggers Using Output Clause:
Output Clause
with Insert Command:
INSERT into emp_details ( [EmpID], EmpName, Salary )
OUTPUT
Inserted.[EmpID], Inserted.EmpName, Inserted.Salary
VALUES (208, 'Delton', 15000);
|
Result:
