In this article, we will discuss about the concept of
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
- If any record is being deleted from the main table, an entry of the record being deleted, is inserted in the
- If any record is being updated in the main table, an entry of that record (before it was updated), is added to the
DELETEDtable and another entry of that record (after it was updated), is inserted into the
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:
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.
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
Delete trigger and Inserted & 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 in the
Update trigger and Inserted & 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
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
Deleted table. Hope you enjoyed reading it…!!!