AFTER UPDATE Triggers in SQL Server

The SQL Server AFTER UPDATE trigger will fire after the Update operation is completed on a table. SQL After UPDATE Triggers not Supported on Views. For this SQL Server After Update Triggers demo, we use the below-shown tables.

Here, our task is to create an AFTER UPDATE TRIGGER in SQL Server on this Employee table. By using this SQL Server After Update Trigger, we want to Update/Insert the records in the Employee Auditable based on the Update action that happened on the employee table

And our Employee Table Audit has also contained the same 14 records, along with the Update Time, and the Action performed NULL Columns.

After UPDATE Triggers in SQL Server Example

In this example, we will create an After update Triggers in SQL Server on the Employee table using the CREATE TRIGGER statement.

Remember, this SQL Server After Update Triggers will fire after the Update operation performed on the Employee table. Once it completes the updating of the Employee table, it will start inserting/updating into the Employee audit table. And if the trigger fails to update the Employee table, then it won’t insert into the Audit table.

TIP: You can refer TRIGGERS, AFTER INSERT TRIGGERS, and AFTER DELETE TRIGGERS article in SQL Server.

-- Example for After UPDATE Triggers in SQL Server

CREATE TRIGGER AfterUPDATETrigger on [EmployeeTable]
FOR UPDATE 
AS DECLARE @EmpID INT,
           @EmpName VARCHAR(50),
	   @EmpEducation VARCHAR(50),
	   @EmpOccupation VARCHAR(50),
	   @EmpYearlyIncome DECIMAL (10, 2), 
	   @EmpSales DECIMAL (10, 2), 
	   @ActionPeformed VARCHAR(50);

SELECT @EmpID = ins.ID FROM INSERTED ins;
SELECT @EmpName = ins.Name FROM INSERTED ins;
SELECT @EmpEducation = ins.Education FROM INSERTED ins;
SELECT @EmpOccupation = ins.Occupation FROM INSERTED ins;
SELECT @EmpYearlyIncome = ins.YearlyIncome FROM INSERTED ins;
SELECT @EmpSales = ins.Sales FROM INSERTED ins;
  
IF UPDATE(YearlyIncome)
BEGIN
     SET @ActionPeformed = 'Updated Yearly Income'
END
 
IF UPDATE(Sales)
BEGIN
      SET @ActionPeformed = 'Updated Sales'
END
INSERT INTO [EmployeeTableAudit]( 
       ID
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[Update Time]
      ,[ActionPerformed])
VALUES (@EmpID,
        @EmpName,
	@EmpEducation,
	@EmpOccupation,
	@EmpYearlyIncome,
	@EmpSales,
	GETDATE(),
	@ActionPeformed);
PRINT 'We Successfully Fired the AFTER UPDATE Triggers in SQL Server.'
GO

First, we used the DECLARE Statement to declare the required variables

AS DECLARE @EmpID INT,
           @EmpName VARCHAR(50),
	   @EmpEducation VARCHAR(50),
	   @EmpOccupation VARCHAR(50),
	   @EmpYearlyIncome DECIMAL (10, 2), 
	   @EmpSales DECIMAL (10, 2), 
	   @ActionPeformed VARCHAR(50);

Next, we used the SELECT Statement to select the inserted/updated records. The following statements will select one record from inserted values.

SELECT @EmpID = ins.ID FROM INSERTED ins;
SELECT @EmpName = ins.Name FROM INSERTED ins;
SELECT @EmpEducation = ins.Education FROM INSERTED ins;
SELECT @EmpOccupation = ins.Occupation FROM INSERTED ins;
SELECT @EmpYearlyIncome = ins.YearlyIncome FROM INSERTED ins;
SELECT @EmpSales = ins.Sales FROM INSERTED ins;

Next, we used the IF Statement check whether we are updating the YearlyIncome or Sales. And based on the action, we want to update the @ActionPerformed column data as following. This information might be helpful for the audit

IF UPDATE(YearlyIncome)
BEGIN
     SET @ActionPeformed = 'Updated Yearly Income'
END
 
IF UPDATE(Sales)
BEGIN
      SET @ActionPeformed = 'Updated Sales'
END

Let me show you the newly created trigger by opening the Object Explorer -> Go to the SQL Tutorial Database -> Find and expand the Employee Table -> and then expand the Triggers Folder

For the SQL Server After Update Triggers demo, we are updating the Yearly Income of a single column, whose name = ‘Tutorial Gateway’ to check the After update Trigger is triggered or not.

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 1252565
	WHERE NAME = N'Tutorial Gateway'

From the above, you can see that our After Update Trigger is triggered, and also inserted one record into the Audit Table. Please use this query to check the Updated records in Employee table

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeTable]

Next, check the records in the Employee table Audit using the following query.

SELECT [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[ServerName]
      ,[ServerInstanceName]
      ,[Insert Time]
      , [Update Time] 
      ,[ActionPerformed]
  FROM [EmployeeTableAudit]
ORDER BY ID

From the above screenshot, you can see that the trigger has inserted one more record with the same ID 7 with updated information.

After UPDATE Triggers in SQL Server Example 2

How to Update the existing row in the audit table (triggered table), instead of Inserting a new record using the After Update Triggers in SQL Server. And here, we will modify the trigger that we created in our previous example.

-- Example for After UPDATE Triggers in SQL Server

CREATE TRIGGER AfterUPDATETrigger on [EmployeeTable]
FOR UPDATE 
AS DECLARE @EmpID INT, 
           @EmpName VARCHAR(50),
           @EmpEducation VARCHAR(50),
	   @EmpOccupation VARCHAR(50),
	   @EmpYearlyIncome DECIMAL (10, 2), 
	   @EmpSales DECIMAL (10, 2), 
	   @ActionPeformed VARCHAR(50);

SELECT @EmpID = ins.ID FROM INSERTED ins;
SELECT @EmpName = ins.Name FROM INSERTED ins;
SELECT @EmpEducation = ins.Education FROM INSERTED ins;
SELECT @EmpOccupation = ins.Occupation FROM INSERTED ins;
SELECT @EmpYearlyIncome = ins.YearlyIncome FROM INSERTED ins;
SELECT @EmpSales = ins.Sales FROM INSERTED ins;
IF UPDATE(YearlyIncome)
BEGIN
     SET @ActionPeformed = 'Updated Yearly Income'
END
IF UPDATE(Sales)
BEGIN
      SET @ActionPeformed = 'Updated Sales'
END
UPDATE [EmployeeTableAudit]
     SET [Name] = @EmpName, 
         [Education] = @EmpEducation, 
	 [Occupation] = @EmpOccupation,
	 [YearlyIncome] = @EmpYearlyIncome, 
	 [Sales] = @EmpSales, 
	 [Update Time] = GETDATE(), 
	 [ActionPerformed] = @ActionPeformed
WHERE ID = @EmpID;
PRINT 'We Successfully Fired the Second AFTER UPDATE Triggers in SQL Server.'
GO

From the above code snippet, you can see that we are using the UPDATE Statement to update the existing records in the Audit table.

Next, let me update the Yearly Income for multiple records in the employee table

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 1252565,
	    [Sales] = 699
	WHERE [Occupation] = N'Management' OR
	      [Education] = N'Partial High School'

From the above screenshot, you can see that our after update trigger in Sql Server has fired. However, it is updating a single record rather than all the 7 records. Let us see the Employee table

Next, check with the Employee Audit table.

From the above screenshot, you can see that the trigger updated one and only one record.

After UPDATE Triggers in SQL Server Example 3

In this example, we will show you, How to update all the records into the audit table (triggered table) using the After update Triggers in SQL Server. Here, we will modify the above trigger.

-- Example for After UPDATE Triggers in SQL Server

CREATE TRIGGER AfterUPDATETrigger on [EmployeeTable]
FOR UPDATE 
AS DECLARE @ActionPeformed VARCHAR(50)

IF UPDATE(YearlyIncome)
BEGIN
     SET @ActionPeformed = 'Updated Yearly Income'
END
IF UPDATE(Sales)
BEGIN
      SET @ActionPeformed = 'Updated Sales'
END

INSERT INTO [EmployeeTableAudit]( 
       [ID]
      ,[Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[Update Time]
     ,[ActionPerformed])
SELECT  ID,
	Name,
	Education,
	Occupation,
	YearlyIncome,
	Sales,
	GETDATE(),
	@ActionPeformed
FROM INSERTED;
PRINT 'We Successfully Fired Our Third AFTER UPDATE Triggers in SQL Server.'
GO

From the above code, you can see we are using the INSERT INTO SELECT Statement to pick all the records that are inserted into the Employee table. Then we insert those records into the Audit table.

Next, let me update the yearly income of multiple records in the employee table

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 150000,
	    [Sales] = 888
	WHERE [Occupation] = N'Management'

As you can see, our SQL after update trigger fired, and also inserted all the records into the audit table. Let us see the Employee table

Next, check with the Employee Audit table.

From the above screenshot, you can see that the trigger has inserted 4 new records with the same ID’s (2, 7, 10, and 12) with the same ID 7 with updated information. It might not be the case you required so, let us see the next example.

After UPDATE Triggers in SQL Server Example 4

Update all the records in the audit table (triggered table) using the After Update Triggers. For this, we are using the MERGE Statement.

-- Example for After UPDATE Triggers in SQL Server

CREATE TRIGGER AfterUPDATETrigger on [EmployeeTable]
FOR UPDATE 
AS DECLARE @ActionPeformed VARCHAR(50)

MERGE [EmployeeTableAudit] AS AuditTab
USING (SELECT * FROM INSERTED) AS Emp
ON AuditTab.ID = emp.ID
WHEN MATCHED THEN
UPDATE SET AuditTab.[Name] = Emp.Name, 
         AuditTab.[Education] = Emp.Education, 
	 AuditTab.[Occupation] = Emp.Occupation,
	 AuditTab.[YearlyIncome] = Emp.YearlyIncome, 
	 AuditTab.[Sales] = Emp.Sales, 
	 AuditTab.[Update Time] = GETDATE(), 
	 AuditTab.[ActionPerformed] = 'Updated Successfully';

PRINT 'We Successfully Fired Our Fourth AFTER UPDATE Triggers in SQL Server.'
GO

Next, let me perform updating on the employee table

UPDATE [EmployeeTable]
	SET [YearlyIncome] = 386000,
	    [Sales] = 5525.45
	WHERE [Occupation] = N'Management' OR
	      [Education] = N'Bachelors'

As you can see, our update trigger has fired and also updated all the records in the audit table. Let us see the Employee table

Next, check with the Employee Audit table.

As you can see that the trigger has updated all the records.