Inserted and Deleted Tables in SQL

Introduction

This article will teach us about Inserted and Deleted Tables in SQL Server. Let's start with their brief description.

As per MSDN

DML trigger statements use two special tables: the deleted table and the inserted table. You can use these temporary, memory-resident tables to test certain data modifications' effects and set conditions for DML trigger actions. SQL Server automatically creates and manages these tables.

For multiple DML operations being done in the trigger, these tables are affected in the following manner.

• If any record is being inserted into the main table, a new entry of the document being created is also inserted into the INSERTED table.  
• If any record is being deleted from the main table, an entry of the record is being deleted is inserted into the DELETED table.  
• If any record is being updated in the main table, an entry of that record (before it was updated) is added to the DELETED table, and another entry of that record (after it was updated) is inserted into the INSERTED table.

Now let's test these cases. For this, we create a new table called Products and a DML trigger on it. This trigger will display records from the following tables whenever a DML operation is performed on the table. Our trigger will be the same for Insert/Update/Delete operations.

• Data from the main table
• Data from Inserted table
• Data of Deleted table

Our trigger will look like the following.

Now let's test the cases.

Create a trigger and Inserted and Delete tables

Let's insert two new records into the table and discuss the output.

As we discussed above, inserting a new record into the main table adds the same record in the Inserted table, but no record is added in the Deleted table. In our case, the insertion of the first record with ProductId = 1 adds the same record to the Inserted table. When the second record is inserted, the trigger is executed again, and only the second record is added to the Inserted table. In both cases, there is no insertion in the Deleted table.

Delete trigger and Inserted and Deleted tables

Now let's execute the Delete query and see the output.

As we can see above, when we delete the record with ProdcutId = 2, its copy is inserted into the Deleted table. But no record is added to the Inserted table.

Update trigger and Insert and Delete tables

Now let's update a record in the table and see the results.

As we discussed above, when we update a record, its copy is added to the Deleted table before it is updated. Its new copy is added to the Inserted table after the record is updated in the main table. In our example, the record with ProductId = 1 is added to the Deleted table with the old values, and the same record with the new values is added to the Inserted table.

So this was about the concept of the Inserted and Deleted tables. One important point to be discussed here is that the OUTPUT clause in SQL always has access to these tables. We will discuss that clause in my next article.

Conclusion

In this article, we learned about Inserted and Deleted Tables in SQL.