SQL Server : trigger how to read value for Insert, Update, Delete

Logical Tables in Sql Server

Data Manipulation Language (DML) Triggers have access to the two special logical Tables named INSERTED and DELETED. These are the temporary tables managed by Sql Server. Structure of these tables will be same as that of the table on which the DML action is fired and holds the old and new values of the rows which are modified by the DML statement.

DELETED logical table will hold the rows which are deleted from the trigger table (i.e. the table on which the trigger is defined) by the DELETE or UPDATE operation. An UPDATE DML operation is internally executed as first the deletion of the existing record and then insertion of the new record.

INSERTED logical table will hold the rows which are inserted by the INSERT and UPDATE statement.

SELECT
  CASE WHEN inserted.primaryKey IS NULL THEN 'This is a delete'
       WHEN  deleted.primaryKey IS NULL THEN 'This is an insert'
                                        ELSE 'This is an update'
  END  as Action,
  *
FROM
  inserted
FULL OUTER JOIN
  deleted
    ON inserted.primaryKey = deleted.primaryKey