Let us suppose if we write a trigger on the table on insert or delete or update. So on insertion of a record into that table, the inserted table will be created automatically by the database, on deletion of record from that table; the deleted table will be created automatically by the database.
These two tables, inserted and deleted, are called magic tables.
Magic tables are used to put all the deleted and updated rows. We can retrieve the column values from the deleted rows using the keyword "deleted".
These are not physical tables, only internal tables.
This Magic table is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only.
But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.
Using with Triggers:
If you have implemented trigger for any Tables then,
- Whenever you insert a record
into a table, that record will be in the INSERTED Magic table.
- Whenever you update the
record in that table, that existing record will be in the DELETED Magic
table and the modified new data with be in the INSERTED Magic table.
- Whenever you delete a record
in that table, that record will be in the DELETED Magic table only.
These magic tables are used inside the Triggers for tracking the data transaction.
8. Using Non-Triggers:
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.
Example:-
Create table Employee
(
Emp_Id INT
IDENTITY(1,1) NOT NULL,
Emp_Name varchar(50),
Salary decimal(10,2)
)
INSERT into Employee(Emp_Name, Salary)
SELECT 'Tom', 50087
UNION
SELECT 'Rascle', 8000
CREATE TRIGGER Emp_Trg
ON
Employee
FOR INSERT
AS
BEGIN
SELECT * FROM INSERTED -- SHOW DATA IN INSERTED VIRTUAL TABLE
SELECT * FROM DELETED -- SHOW DATA IN DELETED VIRTUAL TABLE
END
--Now insert a new
record in Employee table
--to check data
within 'Inserted' virtual table
INSERT INTO Employee(Emp_Name, Salary) VALUES ('Amar', 40000)
Deleted Magic table
Create the trigger
for update as shown below:
ON Employee
FOR UPDATE
AS
begin
SELECT * FROM INSERTED -- show data in INSERTED virtual table
SELECT * FROM DELETED -- show data in DELETED virtual table
end
Now run the following
update query on Employee table to check the values of Inserted and Deleted
virtual tables.
1
2
|
Update
Employee set Salary=60000 where Emp_Id=3
SELECT
* FROM
Employee
|
No comments:
Post a Comment