WHAT IS INSERTED, DELETED LOGICAL TABLE IN SQL SERVER

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 1523  View(s)
Ratings:
Rate this:
Please explain me how to get inserted and deleted tables in sqlserver.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: What is Inserted, Deleted Logical table in SQL Server

    Sqlserver have  Inserted and Deleted logical tables. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

    Inserted logical Table

    The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.

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

     

    CREATE TRIGGER TRIGGER_Employee_Insert
    

    ON Employee

    FOR INSERT

    AS

    begin

    SELECT * FROM INSERTED -- show data in Inserted logical table

    SELECT * FROM DELETED -- show data in Deleted logical table

    end

    Now insert a new record in Employee table to see data with in Inserted logical table.

     INSERT INTO Employee(Emp_Name,Emp_Sal) VALUES('Manoj',5000)
    

    SELECT * FROM Employee

    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.

     

    Create TRIGGER TRIGGER_Employee_Insert
    

    ON Employee

    FOR UPDATE

    AS

    begin

    SELECT * FROM INSERTED -- show data in Inserted logical table

    SELECT * FROM DELETED -- show data in Deleted logical table

    end

    --Now update the record in Employee table to see data with in Inserted and Deleted logical tables

    Update Employee set Emp_Sal=9200,Emp_Name='Shukla' where Emp_ID=7

    SELECT * FROM Employee

    We could not create the logical tables or modify the data with in the logical tables. Except triggers, When you use the OUTPUT clause in your query, logical tables are automatically created and managed by SQL Server. OUTPUT clause also has access to Inserted and Deleted logical tables just like triggers.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!