Introduction to SQL UPDATE Trigger
UPDATE Trigger in standard query language (SQL) is a stored procedure on a database object that gets fired automatically before or after an UPDATE statement has been successfully executed on the said database object. For those new to SQL, an UPDATE statement is used for modifying data records in an existing data table.
UPDATE Triggers are usually used in situations when we want to track details of modification on certain database values. For example, we want to capture the time of login and logout of a user from a website, restaurant, office etc. In the former case, we can use BEFORE UPDATE Trigger and AFTER UPDATE Trigger for the latter..
In this post, we will be discussing UPDATE Trigger in detail with the help of some examples. To begin with, let us learn the syntax and parameters used for creating an UPDATE Trigger in SQL.
Syntax and parameter:
The basic syntax used for writing an UPDATE Trigger in SQL is as follows :
CREATE TRIGGER [schema_name. ] trigger_name ON table_name
{AFTER| BEFORE | INSTEAD OF } UPDATE
AS
BEGIN
[SET NOCOUNT {ON/OFF}]
{SQL statements}
END
The parameters used in the above-mentioned syntax are as follows :
- schema_name: schema_name here corresponds to the name of the schema where the new update trigger will be created. Schema name is completely an option and hence can be ignored. When we do not mention schema_name, the trigger gets created on the current or default schema.
- trigger_name: trigger_name is the name of the new update trigger which we will be creating.
- table_name: table_name is the name of the table in the mentioned schema on which the new update trigger will be created.
- AFTER | BEFORE | INSTEAD OF: This argument is to register the time when the trigger will be invoked, if it will be invoked before or after the said UPDATE statements have been executed.
- SQL statements: SQL statements are a set of SQL operations that form the body of an UPDATE trigger. These statements are performed just before or after (based on specification) the trigger.
Having discussed the syntax and parameters used for creating UPDATE triggers in SQL, let us try a few examples to understand it in great detail.
Examples of SQL UPDATE Trigger
In order to illustrate working of UPDATE triggers in SQL, what could be better than trying some examples on a dummy table. Ergo, let us create a table called “orders” that contains details such as ordered item, ordered_at, shipped_at, status, etc. We can use the following CREATE command to create the said table.
Code:
CREATE TABLE orders(
order_id INT NOT NULL IDENTITY PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
product_id INT,
amount NUMERIC,
ordered_at DATETIME,
shipped_at DATETIME,
status VARCHAR(100)
);
Having created the said table, let us insert a few records in it to work with using the following insert statement.
Code:
INSERT INTO [master].[dbo].[orders]
([customer_name]
,[product_id]
,[amount]
,[ordered_at]
,[shipped_at]
,[status])
VALUES
('R K Sharma',11,456,'2020-06-06',NULL,NULL),
('B K Varma',17,600,'2020-06-16',NULL,NULL),
('Kritika Singh',20,5600,'2020-06-18',NULL,NULL),
('R K Sharma',12,1200,'2020-06-16',NULL,NULL)
GO
The data in the orders table after successful insertion, looks something as follows:
Code:
SELECT * FROM orders;
Now we are all set to try a few examples on UPDATE TRIGGER using this table.
Example #1: Create a trigger in SQL, which automatically updates the date and time of shipping once the order status has been changed to shipped.
Code:
CREATE TRIGGER update_trigger ON orders
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE orders set shipped_at = GETDATE()
from orders b
INNER JOIN inserted i on b.order_id=i.order_id
AND i.status = 'Shipped'
END
GO
The command has successfully created an UPDATE trigger, that gets invoked after successful update statement execution. The trigger name can be seen under the orders table in the object explorer.
Now let us try a few update queries on the orders table.
Code:
UPDATE orders
SET status= 'Shipped'
WHERE order_id = 1;
The update query returned successful. Ergo, our update_trigger must have been invoked and shipped_at date must have been populated. We can check this using a SELECT query.
Code:
SELECT * FROM orders;
Code:
UPDATE orders
SET status= 'Shipped'
WHERE amount > 5000;
Similar to the previous query, the query returned successful. Therefore, our update_trigger must have been invoked and shipped_at date must have been populated. We can check this using a SELECT query.
Code:
SELECT * FROM orders;
Example #2: Create a trigger in SQL, which automatically gets invoked and sets shipped_at time to NULL and status to ‘Cannot be Shipped’ when the order value is less than 500.
Code:
CREATE TRIGGER update_trigger ON orders
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE orders
set shipped_at = NULL, status = 'Cannot be Shipped'
from orders b
INNER JOIN inserted i on b.order_id=i.order_id
AND i.amount < 500
END
GO
This update trigger is a variation of the previous one. In this case, the trigger gets fired before the update queries are executed.
Let’s try an update query to illustrate it further.
Code:
UPDATE orders
SET status = 'Shipped'
WHERE amount < 500;
The results of the update query can be checked using a SELECT statement.
Code:
SELECT * FROM orders;
Finally, an update trigger can be deleted or dropped using a DROP TRIGGER command.
Code:
DROP TRIGGER update_trigger;
Conclusion
An UPDATE TRIGGER in SQL gets invoked before or after an update statement gets executed on the said database object. It is most commonly used to track and log time of modifications in the database.
Recommended Articles
This is a guide to SQL UPDATE Trigger. Here we discuss the introduction, syntax, parameters, examples with code implementation respectively. You may also have a look at the following articles to learn more –
