Tuesday 9 July 2013

deleted Magic table in sql server

Deleted logical Table::

                   The Deleted table holds the recently deleted or updated values means old data values.
Hence old updated and deleted records are inserted into the Deleted table.

Example::

   Suppose we have emp_info . Now We need to create two triggers to see data with in logical tables
Inserted and Deleted.

Step 1::Create Table

create table emp_info (empno int,ename varchar(100),manage_id int)

Step 2 ::CREATE TRIGGER on emp_info


CREATE TRIGGER trg_Emp_Ins
ON emp_info
FOR UPDATE
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end

Step 3::Now Update a record in emp_info table to see data with in deleted logical table.


update emp_info set ename='Sandip Gaikwad', manage_id=3 where empno=3
SELECT * FROM emp_info

Step 4::OUTPUT

Other Popular Posts ::

No comments:

Post a Comment

if you have any doubt any suggestions do comment