Inserted and Deleted tables in SQL


In this article, we will discuss about the concept of 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 tables. 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 different DML operations being performed in the trigger, these tables are affected in the following manner:

  • If any record is being inserted in the main table, a new entry, of the record being created, is also inserted in the INSERTED table.
  • If any record is being deleted from the main table, an entry of the record being deleted, is inserted in 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 same for Insert/Update/Delete operations. The purpose of this trigger will be to display records from following tables, whenever any 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:

Inserted & Deleted tables in SQL

Now let’s test the cases.

Create trigger and Inserted & Deleted tables: Let’s insert 2 new records in the table and discuss the output.

Inserted & Deleted tables in SQL

As we discussed above, insertion of new record in 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 in Inserted table. When second record is inserted, trigger is executed again and only second record is added in the Inserted table. In both the cases, there is no insertion in the Deleted table.

Delete trigger and Inserted & Deleted tables: Now let’s execute the Delete query and see the output.

Inserted & Deleted tables in SQL

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 in the Inserted table.

Update trigger and Inserted & Deleted tables: Now let’s update a record in the table and see the results.

Inserted & Deleted tables in SQL

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 have access to these tables. We will discuss about this clause in my next article. So this was about the concept of Inserted and Deleted table. Hope you enjoyed reading it…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s