In this article I will explain with example, how to get the Inserted, Updated and Deleted Row column values such as ID field in Insert, Update and Delete SQL Server Triggers.
This tutorial is applicable for all versions of SQL Server i.e. 2005, 2008, 2012, 2014, 2019, 2022 etc.
 
 

Database

I have made use of the following table Customers with the schema as follows.
SQL Server: Get Inserted, Updated and Deleted Row values in Insert, Update and Delete Triggers
 
I have already inserted few records in the table.
SQL Server: Get Inserted, Updated and Deleted Row values in Insert, Update and Delete Triggers
 
Below is the CustomerLogs table which will be used to log the Trigger actions.
SQL Server: Get Inserted, Updated and Deleted Row values in Insert, Update and Delete Triggers
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

Triggers

Triggers are database operations which are automatically performed when an action such as Insert, Update or Delete is performed on a Table or a View in database.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers.
 
 

Types of Triggers

There are two types of Triggers. After and Instead of Triggers.

After Triggers

These triggers are executed after an action such as Insert, Update or Delete is performed.
 

Instead of Triggers

These triggers are executed instead of any of the Insert, Update or Delete operations. For example, let’s say you write an Instead of Trigger for Delete operation, then whenever a Delete is performed the Trigger will be executed first and if the Trigger deletes record then only the record will be deleted.
 
 

Getting Inserted, Updated and Deleted Row values in Insert, Update and Delete SQL Server Triggers

Now I will explain you with examples how to Get Inserted, Updated and Deleted Row column values such as ID field in Insert, Update and Delete Triggers in SQL Server.

Get Inserted Row value in Insert Trigger

Below is an example of an After Insert Trigger. Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_INSERT]
       ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
 
       SELECT @CustomerId = INSERTED.CustomerId       
       FROM INSERTED
 
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Inserted')
END
 

Get Updated Row value in Update Trigger

Below is an example of an After Update Trigger. Whenever a row is updated in the Customers Table, the following trigger will be executed. The updated record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the updated record. In order to find which column is updated, you will need to use UPDATE function and pass the column name of the Table to it.
The UPDATE function will return TRUE for a column, if its value was updated else it will return false.
Finally, based on which column of the record has been updated a record (containing the CustomerId and the appropriate action) is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_UPDATE]
       ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
       DECLARE @Action VARCHAR(50)
 
       SELECT @CustomerId = INSERTED.CustomerId       
       FROM INSERTED
 
       IF UPDATE(Name)
       BEGIN
              SET @Action = 'Updated Name'
       END
 
       IF UPDATE(Country)
       BEGIN
              SET @Action = 'Updated Country'
       END
 
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, @Action)
END
 

Get Deleted Row value in Delete Trigger

Below is an example of an After Delete Trigger. Whenever a row is delete in the Customers Table, the following trigger will be executed. The deleted record is available in the DELETED table.
The following Trigger is fetching the CustomerId of the deleted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_DELETE]
       ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT
 
       SELECT @CustomerId = DELETED.CustomerId
       FROM DELETED
 
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Deleted')
END
 
 

Screenshot

The following screenshot displays the Log table after the above Triggers were executed.
SQL Server: Get Inserted, Updated and Deleted Row values in Insert, Update and Delete Triggers