In this article I will explain with simple example, how to write an Instead Of Update Trigger in SQL Server.
This tutorial is applicable for all versions of SQL Server i.e. 2005, 2008, 2012, 2014, etc.
 
Database
I have made use of the following table Customers with the schema as follows.
SQL Server: Instead Of Update Trigger Example
 
I have already inserted few records in the table.
SQL Server: Instead Of Update Trigger Example
 
Below is the CustomerLogs table which will be used to log the Trigger actions.
SQL Server: Instead Of Update Trigger Example
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 
Instead Of Update Triggers
These triggers are executed instead of any of the Insert, Update or Delete operations.
For example consider an Instead of Trigger for Update operation, whenever an Update is performed the Trigger will be executed first and if the Trigger updates record then only the record will be updated.
Below is an example of an Instead Of Update Trigger. Whenever anyone tries to update a row from the Customers table the following trigger is executed.
Inside the Trigger, I have added a condition that if the CustomerId column of the record is updated then such a record must not be updated and an error must be raised. Also a record is inserted in the CustomerLogs table.
If any other column is updated then an update query is executed which updates the record and a record is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_InsteadOfUPDATE]
       ON [dbo].[Customers]
INSTEAD OF UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @CustomerId INT, @Name VARCHAR(50), @Country VARCHAR(50)
 
       SELECT @CustomerId = INSERTED.CustomerId,
              @Name = INSERTED.Name,
              @Country = INSERTED.Country       
       FROM INSERTED
 
       IF UPDATE(CustomerId)
       BEGIN
              RAISERROR('CustomerId cannot be updated.', 16 ,1)
              ROLLBACK
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'CustomerId cannot be updated.')
       END
       ELSE
       BEGIN
              UPDATE Customers
              SET Name = @Name,
              Country = @Country
              WHERE CustomerId = @CustomerId
 
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'InsteadOf Update')
       END
END
 
The following error message shown when record’s CustomerId field is updated.
SQL Server: Instead Of Update Trigger Example
 
The following screenshot displays the Log table after the Instead Of Update Trigger is executed.
SQL Server: Instead Of Update Trigger Example