Inserted and Deleted Tables in SQL Server

Recently i have attended a technical interview for an open position with a reputed firm. and got rejected because of few of my mistakes i think.

At first i was confused, the answer was something i already known. Even though i answer wrongly with confusion.

The question was how the Records are handled in an UPDATE trigger when a bulk update of records occurs. It was quite simple that records are handled as collection of rows or table. I was aware of INSERTED and DELETED special temporary tables, memory-resident tables in SQL Server to test the effects of certain data modifications and to set conditions for trigger actions.

Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® 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 trigger actions; however, you cannot alter the data in the tables directly.

The inserted and deleted tables are used primarily in triggers to:

• Extend referential integrity between tables.
• Insert or update data in base tables underlying a view.
• Check for errors and take action based on the error.
• Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

At that wrong moment instead of thinking about things explained above, i thought about like, we use UPDATE(<Column Name>) to identify whether the column has updated or not right and simply said ” as a single row”  and my interviewer laughed on my answer and explained it is handles  collection of rows.

Ohhh Shit!!! sudden stike in my mind. I made a stupid answer. I didnt think about the functionalities of INSERTED and UPDATED table at that point, really at that point i didnt remember about it or my brain couldn’t fetch it on time. I was aware of INSERTED and UPDATED tables and functionalities, but it’s human nature some times we couldn’t remember or couldn’t get what we were doing.  A mistake, but i learned from it, next onwards i am sure, i will be pretty careful before making an answer.  🙂 :-).

So that’s it. I blown of that interview.. 

 Note  If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

MSDN Reference Link