Inserted and deleted virtual tables

There are two virtual tables?the "inserted" table and the "deleted" table?that are automatically created when a trigger is fired. These two tables can tell you the behind-the-scenes story of your trigger transaction. Each "inserted" table holds copies of the affected data being inserted or updated, while each "deleted" table holds it's data based upon what data is being deleted or updated.

�

Both the "inserted" and "deleted" tables occur whether the transaction is INSERT, UPDATE, or DELETE. Each table informs you of its part in the transaction, but you must access both tables to view the complete transaction history.

�

Suppose you want to write triggers to cover all three instances of a transaction. You can create these triggers independent of one another or, by interpolating a transaction history, you can create a single trigger and thereby maintain one object instead of three.

�

CREATE�TRIGGER�tr_iud�ON�tabx�

FOR�INSERT,�UPDATE,�DELETE

AS

�

DECLARE�@tiTranType�TINYINT

�

IF�((SELECT�COUNT(*)�FROM��deleted)�>�0�AND�

����(SELECT�COUNT(*)�FROM��inserted)�>�0)

������BEGIN

��������SET�@tiTranType�=�1

��������--�PRINT�'Update�transaction'

������END

�

IF�((SELECT�COUNT(*)�FROM��deleted)�=�0�AND�

����(SELECT�COUNT(*)�FROM��inserted)�>�0)

������BEGIN

��������SET�@tiTranType�=�2

��������--�PRINT�'Insert�transaction'

������END

�

IF�((SELECT�COUNT(*)�FROM��deleted)�>�0�AND�

����(SELECT�COUNT(*)�FROM��inserted)�=�0)

������BEGIN

��������SET�@tiTranType�=�3

��������--�PRINT�'Delete�transaction'��

������END

�

With the variable @tiTranType now set, you can use it to perform conditional processing, based upon its value, further down in your trigger code.

�