The INSTEAD OF UPDATE triggers are fired before the execution starts. So, you can use this trigger to pass the value for Identity Columns, or Updating different table etc. In this article we will show you, How to write the INSTEAD OF UPDATE Triggers in SQL Server with example. For this demonstration, We are going to use the tables that we shown below. As you can see from the below screenshot that our Employee table holds 14, and our task is to create an INSTEAD OF UPDATE TRIGGER on this Employee table. And by using this trigger, we want to restrict the records Update
TIP: You can refer INSTEAD OF INSERT Triggers in SQL Server article to understand the INSTEAD OF INSERT Trigger, and INSTEAD OF UPDATE Triggers in SQL Server article for INSTEAD OF UPDATE Trigger.
and our Employee Table Audit also holds the same 14 records, along with the Server name, Server Instance name, and Insert Time (Audit Information).
NOTE: SQL Instead of Update Triggers can be created in Tables, and Views. In general, we us these triggers on Views
Instead Of UPDATE Triggers in SQL Server Example
In this example we will show you, How to create an Instead of update Triggers in SQL Server. For instance, if you want to restrict the users from updating the records in original table, and you want those updated logs in another historical table then you can use this trigger.
Here, we will create a trigger on Employee table using the CREATE TRIGGER Statement. From the below code snippet you can see that, we are using the INSERT INTO SELECT Statement to select all the records that are inserting into Employee table, and then We are inserting those records into the Audit table. It means, when the user update any Employee table record then trigger will insert those records into audit table, and kept the Employee table unchanged.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
-- Example for INSTEAD OF UPDATE Triggers in SQL Server USE [SQL Tutorial] GO CREATE TRIGGER InsteadOfUPDATETriggerExample on [EmployeeTable] INSTEAD OF UPDATE AS DECLARE @ActionPeformed VARCHAR(50) IF UPDATE(YearlyIncome) BEGIN SET @ActionPeformed = 'Updated Yearly Income' END ELSE 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 First INSTEAD OF UPDATE Triggers in SQL Server.' GO |
OUTPUT
Let me show you the newly created trigger by opening the Object explorer -> Go to the SQL Tutorial Database -> Go and expand the Employee Table -> and then expand the Triggers Folder
For the demonstration purpose we are updating the Yearly Income as 123,456, and Sales = 5000 for all the records in Employee table whose Occupation = Professional.
T-SQL CODE
|
1 2 3 4 5 |
USE [SQL Tutorial] GO UPDATE [EmployeeTable] SET [YearlyIncome] = 123456, [Sales] = 5000 WHERE [Occupation] = N'Professional' |
OUTPUT
From the above screenshot you can see that, our trigger is triggered. And, instead of inserting 4 records into the Employee Audit table. Please use the following SQL Query to check the inserted records in Employee table
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 |
USE [SQL Tutorial] GO SELECT [ID] ,[Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [EmployeeTable] |
OUTPUT
Though we updated the Employee table, As you can see from the above screenshot, our Employee table is unchanged. Next, check the records in Employee table Audit using the following query.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [SQL Tutorial] GO SELECT [Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[ServerName] ,[ServerInstanceName] ,[Insert Time] FROM [EmployeeTableAudit] |
OUTPUT
Here, you can see that the trigger has interested 4 new records into Employee audit table
Instead of UPDATE Triggers in SQL Server Example 2
In this example we will show you, How to Update all the records in the audit table (triggered table) using the Instead of Update Triggers in SQL Server. For this we are using the MERGE Statement.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
-- Example for INSTEAD OF UPDATE Triggers in SQL Server USE [SQL Tutorial] GO CREATE TRIGGER InsteadOfUPDATETriggerExample on [EmployeeTable] INSTEAD OF UPDATE AS DECLARE @ActionPeformed VARCHAR(50) IF UPDATE(YearlyIncome) BEGIN SET @ActionPeformed = 'Updated Yearly Income' END ELSE BEGIN SET @ActionPeformed = 'Updated Sales' END 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] = @ActionPeformed; PRINT 'We Successfully Fired Our Second INSTEAD OF UPDATE Triggers in SQL Server.' GO |
OUTPUT
Next, let me perform updating on employee table
T-SQL CODE
|
1 2 3 4 5 6 |
USE [SQL Tutorial] GO UPDATE [EmployeeTable] SET [YearlyIncome] = 111111, [Sales] = 7777 WHERE [Occupation] = N'Clerical' |
OUTPUT
We don’t have to check the Employee table as we all know that, there will be no updates happened in that table. Next, check with the Employee Audit table.
From the above screenshot you can see that, the trigger has updated all the records.
Instead of UPDATE Triggers in SQL Server Example 3
In this example we will show you, How to Update all the records in the Employee table using the Instead Of Update Triggers in SQL Server. For this we are using the INNER JOIN.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Example for INSTEAD OF UPDATE Triggers in SQL Server USE [SQL Tutorial] GO CREATE TRIGGER InsteadOfUPDATETriggerExample on [EmployeeTable] INSTEAD OF UPDATE AS UPDATE [EmployeeTable] SET [EmployeeTable].[Name] = 'Tutorial Gateway', [EmployeeTable].[Education] = ins.Education, [EmployeeTable].[Occupation] = ins.Occupation, [EmployeeTable].[YearlyIncome] = ins.YearlyIncome, [EmployeeTable].[Sales] = 55555 FROM [EmployeeTable] INNER JOIN INSERTED AS ins ON [EmployeeTable].ID = ins.ID PRINT 'We Successfully Fired Our Third INSTEAD OF UPDATE Triggers in SQL Server.' GO |
OUTPUT
ANALYSIS
Below statement will set the name as ‘Tutorial gateway’
|
1 |
SET [EmployeeTable].[Name] = 'Tutorial Gateway', |
Next statement will set the Sales amount as 55,555
|
1 |
[EmployeeTable].[Sales] = 55555 |
It means, what ever you pass the values to the name, and Sales columns, Instead of Update Trigger will insert the ‘Tutorial gateway’, and 55,555. Or you can say, trigger will override the values
Next, let me perform updating on employee table
T-SQL CODE
|
1 2 3 4 5 6 |
USE [SQL Tutorial] GO UPDATE [EmployeeTable] SET [YearlyIncome] = 999999, [Sales] = 88886 WHERE [Occupation] = N'Management' |
OUTPUT
From the above screenshot you can see that, our instead of update trigger has fired, and also updated all the records in the employee table. Let us se the Employee table
Thank you for Visiting Our Blog
Leave a Reply