SQL Server Trigger For Update with Examples

This SQL Server tutorial will illustrate how to create a SQL Server Trigger for Update operation. Additionally, we will take a look at multiple examples where we need to create SQL Server Trigger for Update.

In SQL Server, we can create triggers that can be executed based on various SQL Server events. And one such important event in SQL Server is about updating tables or view data.

Here is the set of topics that we will discuss in this tutorial.

• Create SQL Server Trigger For Update
• Create SQL Server Trigger For Update vs After Update
• SQL Server Trigger For Update Example
• Create SQL Server Trigger For Update and Insert
• Create SQL Server Trigger For Update Insert and Delete
• Create SQL Server Trigger Update Before and After Values

SQL Server Trigger For Update

In SQL Server, we use the UPDATE statement to update the data of a SQL Server table.

In this SQL Server section, we will illustrate how we can create a trigger in SQL Server that will be executed based upon an UPDATE operation. Let us understand this whole concept of creating a trigger for an UPDATE operation using the example in SQL Server.

For the example, we are going to use the Orders table which is given below.

Here we are going to create a trigger in SQL Server which will update the value of lastUpdateDate based upon the update operation performed on the Orders table.

USE eShop
GO

CREATE TRIGGER trgOrdersLastUpdateDate 
ON Orders 
FOR UPDATE
AS
BEGIN
	UPDATE Orders
	SET lastUpdateDate = GetDate()
	FROM Orders o
	INNER JOIN Inserted i
	ON o.order_Id = i.order_id
END

In this example, we have created a trigger named trgOrdersLastUpdateDate which will update the value of the lastUpdateDate column to the current DateTime. And this value will be updated for only that record that we are going to update using the UPDATE statement.

Next, let us run an UPDATE statement on the Orders table to check if the value of the lastUpdateDate column updates automatically. Here is the T-SQL statement for the UPDATE operation.

USE eShop
GO

UPDATE Orders 
SET quantity = 4 
WHERE order_id = 502

The above query will update the quantity of an order from the Orders table whose order_id is 502. Once we execute the UPDATE statement, the trigger will also be executed automatically. And it will update the value of lastUpdateDate having order_id 502.

Here is the resultset of the Orders table after the UPDATE operation in SQL Server.

So, in this section, we have discussed how to create a trigger in SQL Server for UPDATE operation.

Also, read: SQL Server User Permissions

SQL Server Trigger For Update vs After Update

As per the syntax of defining a trigger in SQL Server, we can use either FOR keyword or the AFTER keyword to define a trigger. Moreover, there is no significant difference in using FOR or AFTER keywords.

Eventually, both keywords are utilized to define a SQL Server Trigger which will be activated only when all of the actions listed in the triggering SQL statement have been successfully executed.

However, there is one condition: before the trigger fires, it should successfully pass all the constraint checks and cascade operations. And we cannot define a SQL Server Trigger For Update on view using the FOR or AFTER keyword. We can only create an INSTEAD OF Trigger for view.

And even if try to create a trigger on view in SQL Server using the FOR or AFTER keyword, the SQL Server instance will return an error. Here is a sample example where we created a view named USAStates.

USE GeoNames
GO

CREATE VIEW [USAStates] AS
SELECT id, name AS [State Name]
FROM States
WHERE country_id = 2;
GO

The main goal of creating this view is to get all state names from the States table where the country is the United States.

After this, we are trying to create a simple trigger in SQL Server on this USAStates view using FOR keyword. Here is the SQL query for this execution.

USE GeoNames
GO

CREATE OR ALTER TRIGGER dbo.trgStateUpadte
ON [USAStates]
FOR UPDATE
AS
BEGIN
	DECLARE @msg VARCHAR(60)
 	SELECT @msg = 'UPDATE Operation is performed successfully'
	PRINT @msg
END

Now whenever we execute the above SQL, it will return the following error in SQL Server.

From the error message, SQL Server wants to state the view is not a valid object type while defining the FOR UPDATE Trigger.

Read: Drop stored procedure SQL Server

SQL Server Trigger For Update and Insert

In the previous section, we discussed how we can create a SQL Server Trigger only for UPDATE operation. However, in SQL Server, we can define a trigger that will execute on multiple actions including UPDATE.

So, in this SQL Server section, we will discuss how we can create a trigger in SQL Server for both UPDATE and INSERT operations.

For this task, we are going to use the Customers and CustomersLog table. So whatever operation we will perform on the Customers table will be recorded in the CustomersLog table.

SQL Server Trigger For Update and Insert Example

Here is the table structure for the Customers and CustomersLog table in the eShop database.

Now, let us look at the example where we will define a trigger named trgCustomerUpdateInsert which will insert records in the CustomersLog table. Here is the T-SQL code for the trigger in SQL Server.

USE eShop
GO

CREATE OR ALTER TRIGGER dbo.trgCustomerUpdateInsert
ON dbo.Customers
FOR UPDATE, INSERT
AS
BEGIN
	DECLARE @action VARCHAR(60),
			 @id INT
 
	IF EXISTS (SELECT 0 FROM inserted)
	BEGIN
		IF EXISTS (SELECT 0 FROM deleted)
		BEGIN 
			SELECT @id = INSERTED.id FROM INSERTED
			SELECT @action = 'UPDATED'
			INSERT INTO CustomersLog VALUES (@id, @action)
		END ELSE
		BEGIN
			SELECT @id = INSERTED.id FROM INSERTED
			SELECT @action = 'INSERTED'
			INSERT INTO CustomersLog VALUES (@id, @action)
		END
	END
END

In the above example, we created a trigger that will insert an entry in the CustomersLog table. This entry will be based upon the insert and update operation that we perform on the Customers table.

So, when we perform an INSERT operation, the trigger will make an entry and set the value of the action column as INSERTED. On the other hand, if we perform the UPDATE operation, it will make an entry and set the value of the action column as UPDATED.

Next, let us perform an INSERT and UPDATE operation on the Customers table and see how the CustomersLog table gets updated automatically.

USE eShop
GO

INSERT INTO Customers
VALUES (6, 'Adam', 'Rogers', 'adrogers0@sitemeter.com', 'Texas', 'Austin')
GO

UPDATE Customers
SET city = 'Hollywood'
WHERE id = 3
GO

After performing the INSERT and UPDATE operations, we will get the following entries in the CustomersLog table.

By the end of this SQL Server section, we got a clear idea of how we can create a SQL Server Trigger for Update and Insert.

Read: Alter view in SQL Server

SQL Server Trigger For Update Insert and Delete

In the previous section, we discussed how we can define a trigger for both UPDATE and INSERT operations in SQL Server. In this SQL Server section, we will understand how we can create a SQL Server Trigger for UPDATE, INSERT, and DELETE operations.

For this implementation, we continue with our Customers and CustomersLog table example. However, this time, we will add code for the DELETE operation as well.

Here is the complete T-SQL code for creating the SQL Server Trigger for the Update, Insert, and Delete operations.

Note:- Please note that before creating and executing another trigger on Customers table, we need to disable or delete the existing trigger. In our case, trgCustomerUpdateInsert was an existing triiger for Customers table. So, disabled it by selecting the following option in SQL Server Management Studios.

Databases > eShop > Tables > dbo.Customers > Triggers > trgCustomerUpdateInsert > Right click > Disable

Here is the code for the new trigger for the Customers table in SQL Server.

USE [eShop]
GO

CREATE OR ALTER TRIGGER [dbo].[trgCustomerUpdateInsertDelete]
ON [dbo].[Customers]
FOR UPDATE, INSERT, DELETE
AS
BEGIN
	DECLARE @action VARCHAR(60),
			@id INT
 
	IF EXISTS(SELECT * from INSERTED) AND EXISTS (SELECT * FROM DELETED)
	BEGIN
		SELECT @id = INSERTED.id FROM INSERTED
		SELECT @action = 'UPDATED'
		INSERT INTO CustomersLog VALUES (@id, @action)
	END 

	IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
	BEGIN
		SELECT @id = INSERTED.id FROM INSERTED
		SELECT @action = 'INSERTED'
		INSERT INTO CustomersLog VALUES (@id, @action)
	END
	
	IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
	BEGIN
		SELECT @id = DELETED.id FROM DELETED
		SELECT @action = 'DELETED'
		INSERT INTO CustomersLog VALUES (@id, @action)
	END
END

Here we created a trigger that will make entries in the CustomersLog table based on Update, Insert, and even Delete operations. So, whenever we perform an operation on the Customers table, the trigger will make an entry in the CustomersLog table.

Here is the sample operation that we will perform on the Customers table.

USE eShop
GO

INSERT INTO Customers
VALUES (7, 'Steve', 'Smith', 'ssmith0@sitemeter.com', 'California', 'Los Angeles')
GO

UPDATE Customers
SET city = 'Houston'
WHERE id = 4
GO

DELETE FROM Customers
WHERE id = 2
GO

In the above SQL code, we are performing INSERT, UPDATE, and even the DELETE operation on the Customers table. This will result in adding 3 new records in the CustomersLog table.

Here is the CustomersLog table after all the operations.

So, in this section we have understood how we can define a SQL Server Trigger For Update, Insert, and Delete operations with an example.

Read: View SQL Server Error Logs

SQL Server Trigger Update Before and After Values

In this SQL Server section, we will understand how to create a trigger in SQL Server to fetch values before and after an update operation. Now, for the implementation of this task, we can use INSERTED and DELETED magic tables in SQL Server.

The INSERTED table in SQL Server holds the inserted values. On the other hand, a DELETED table in SQL Server holds all the deleted values. However, when we perform an UPDATE operation, the old value record is deleted and it goes under the DELETED table. And the new value record is inserted and it goes under the INSERTED table.

Now, to understand how we can fetch before and after update values in a SQL Server Trigger, we will take an example of the Products and ProductPriceLogs table.

Here is the ER diagram for the Products and ProductPriceLog table.

For the example demonstration, we will create a trigger that will maintain a product price record in the ProductPriceLogs table based on the update operation performed on the Products table. The T-SQL code for this task in SQL Server is given below.

USE eShop
GO

CREATE OR ALTER TRIGGER trgProductPriceLog
ON Products
FOR UPDATE
AS
BEGIN
	DECLARE @id INT,
			@old_price VARCHAR(255),
			@new_price VARCHAR(255)
	SELECT @id = inserted.id FROM INSERTED
	SELECT @old_price = deleted.price FROM DELETED 
	WHERE id = @id
	SELECT @new_price = inserted.price FROM INSERTED 
	WHERE id = @id
	IF EXISTS(SELECT * FROM ProductPriceLogs WHERE product_id = @id)
	BEGIN
		UPDATE ProductPriceLogs
		SET old_price = @old_price,
		new_price = @new_price
		WHERE product_id = @id

	END
	ELSE
	BEGIN
		INSERT INTO ProductPriceLogs VALUES(@id, @old_price, @new_price)
	END
END

The above code will create a trigger that will either INSERT or UPDATE the price record of a product in the ProductPriceLogs table.

Here is a sample code for updating the price of a record from the Products table whose id is 1 and 3.

USE eShop
GO

UPDATE Products
SET price = '$1176'
WHERE id = 1

UPDATE Products
SET price = '$120'
WHERE id = 3

After performing the update operation on the Products table, we can check how records in the ProductPriceLogs table are maintained.

By the end of this section, we have discussed an example where we understood to fetch and use before and after update values in a SQL Server Trigger.

Conclusion

So, in this section, we understood how to create a SQL Server Trigger For Update. Also, we have discussed multiple examples where we created SQL Server Trigger For Update, Insert, and Delete operations.

Here is the complete list of topics that we have discussed in this tutorial.

• Create SQL Server Trigger For Update
• Create SQL Server Trigger For Update vs After Update
• SQL Server Trigger For Update Example
• Create SQL Server Trigger For Update and Insert
• Create SQL Server Trigger For Update Insert and Delete
• Create SQL Server Trigger Update Before and After Values

You may also like to read the following SQL Server tutorial.

• SQL Server function return table
• Trigger For Delete SQL Server
• Trigger in SQL Server for Insert and Update
• SQL Server Trigger After Insert Update
• Arithmetic operators in SQL Server
• Alter Stored Procedure in SQL Server
• SQL Server scheduled stored procedure