Inserted and Deleted Tables in SQL

Let's start with their brief description. As per MSDN:

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

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 record 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. Our trigger will be the same for Insert/Update/Delete operations. The purpose of this trigger will be displaying records from the following tables, whenever a DML operation is performed on the table:

• Data of main table
• Data of Inserted table
• Data of Deleted table

Our trigger will look like the following:



Now let's test the cases.

Create trigger and Inserted and Deleted tables: Let's insert 2 new records into the table and discuss the output.



As we discussed above, insertion of 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, insertion of the first record with ProductId = 1, adds the same record to 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 the 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 Inserted and Deleted 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 was updated and 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.

One very 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. So this was about the concept of the Inserted and Deleted tables. I hope you enjoyed reading it!