Understanding Triggers in SQL Server
A trigger is an object having a collection of code or codes which fires automatically when an event occurs in the database server. The most used database events which cause the database to execute triggers are DDL(DATA DEFINITION LANGUAGE) Event and DML(DATA MANIPULATION LANGUAGE) Event. DDL Triggers are used for TSQL statements like CREATE, ALTER, DROP, and also some system stored procedures, on the other hand, DML triggers get fired when the user tries to modify or change the data with INSERT, UPDATE, DELETE statements on table or views. These DML triggers can be defined to execute on INSERT, UPDATE, and DELETE operations, or for any combination of these operations. MERGE operation also fires triggers based on operations made within the MERGE statement.
The Trigger definition can either be of type INSTEAD OF or AFTER or FOR. When we perform a write operation on a table, the INSTEAD OF trigger replaces the operation and executes trigger content instead and in the case of AFTER trigger the trigger gets fired once the write operation is completed. Do Note that if the AFTER Trigger fails, the original write operation also fails.
The automated execution of code on every change event is the main plus point of the trigger and also if the trigger code fails, the event which fired the trigger will fail. A few cases where triggers serve their purpose are tracking database changes including schema or object changes, writing table logs, and validation checks and also they can implement custom validation functions, enforcing other business rules, and exert control when performing an insert, update, or delete operations and decide what to do with data. Before moving forward let's create a simple trigger example.
Basic Trigger Example
Consider a case, wherein we want to log all update operations on the Users table and record those operations in the users_log table for our future audit purpose. Let's start by creating a Users table and inserting some data in it.
DROP TABLE IF EXISTS users;
CREATE TABLE users(
id INT IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR(30) not null,
last_name VARCHAR(35),
date_of_birth DATE,
user_type_id INT
);
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Lamont', 'Lantiffe', 3, '1996-10-03');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Xenia', 'Donovin', 4, '1996-10-19');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Eleen', 'Joriot', 4, '1997-01-19');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('De', 'Alpe', 1, '1996-12-04');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Skipton', 'Myrick', 4, '1996-03-23');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Kariotta', 'Sapsforde', 1, '1996-12-10');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Darill', 'De Moreno', 1, '1996-09-22');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Sayre', 'Critch', 3, '1996-07-29');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Chrissy', 'Vedekhov', 3, '1996-08-27');
INSERT INTO users (first_name, last_name, user_type_id, date_of_birth) VALUES ('Dalis', 'Dutnall', 1, '1996-08-20');
DROP TABLE IF EXISTS users_log ;
CREATE TABLE users_log (
users_log_id int identity(1,1) primary key,
users_id int,
first_name_old varchar(30),
first_name_new varchar(30),
last_name_old varchar(35),
last_name_new varchar(35),
date_of_birth_old date,
date_of_birth_new date,
user_type_id_old int,
user_type_id_new int,
action_type varchar(10),
action_time datetime2 not null,
user_name varchar(128)
);
CREATE TRIGGER dbo.TR_U_Users_Audit
on dbo.Users
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.users_log
(users_id, first_name_old, first_name_new, last_name_old, last_name_new, date_of_birth_old, date_of_birth_new, user_type_id_old, user_type_id_new, action_type, action_time, user_name)
SELECT i.id, d.first_name as first_name_old, i.first_name as first_name_new, d.last_name as last_name_old, i.last_name as last_name_new, d.date_of_birth as date_of_birth_old, i.date_of_birth as date_of_birth_new, d.user_type_id as user_type_id_old, i.user_type_id as user_type_id_new,
'Update', SYSUTCDATETIME() action_time,
SUSER_SNAME() AS user_name
FROM Inserted i
FULL JOIN Deleted d
ON i.id = d.id;
END
GO
The above-created triggers' sole purpose is to insert records in Users_Audit Table, one for each UPDATE operation modifying the data in the Users table. It's simple and we can further modify it if we want to track any other details regarding it.
SELECT * FROM users;
UPDATE users SET first_name = 'Leaim', last_name = 'Lantiffe', date_of_birth = '1994-01-13' WHERE id = 1;
SELECT * FROM users_log;
With the above update operation, the old and the new data of the record are captured in the Users_Log Table. The Users_log Table contains both old and new data for the updated user_id 1. We have joined Inserted and Deleted pseudo tables in the triggers to get both the old and new data of the record. Let's understand INSERTED and DELETED pseudo tables more, so we can use them to write better triggers.
INSERTED and DELETED table.
INSERTED and DELETED are special pseudo tables, available for the write operations. This pseudo table is available during the execution of DML events. Insert operation will have INSERTED table, Delete operation will have DELETED table, and Update operation will have both INSERTED and DELETED table. In the case of Insert operations, the INSERTED table will contain new values for each column in a table and the DELETED table will have no data. Similarly, for the Delete operation, the DELETED table will contain old values for each column in a table and the INSERTED table will have no data. And for the Update operation, the INSERTED table will contain new data for each column in a table and the DELETED table will contain old data. This construct makes it easier to identify the type of writing operation being conducted on a table. If both the INSERTED and DELETED table has data, then it was an Update Operation, if only the DELETED table didn't have any data then it's an Insert operation and if the INSERTED table didn't have any data then it's a Delete operation. This helps us in understanding why we had joined INSERTED and DELETED tables in the trigger written above for logging all update operations on the Users Table. Now we will see more examples for each DML operation with the 'AFTER' and 'INSTEAD OF' triggers.
For further examples, let's create two tables one containing the records of Invoices and the other containing the invoice line items for each invoice with minimum columns for the sake of simplicity.
DROP TABLE IF EXISTS invoices;
CREATE TABLE invoices(
id int identity(1,1) primary key,
raised_date date not null,
raised_by int not null,
customer_id int not null,
payment_date date not null,
total_amount numeric(12,2) not null default(0),
is_paid bit not null default(0)
);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 4, 5, '2021-03-03', 71450.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 1, 11, '2021-03-03', 543020.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 2, 2, '2021-03-02', 514290.00, 0);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 3, 10, '2021-03-02', 1233400.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 1, 2, '2021-03-03', 1951707.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 3, 7, '2021-03-02', 39549.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 3, 10, '2021-03-03', 25500.00, 0);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 4, 3, '2021-03-03', 22950.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 4, 9, '2021-03-03', 818274.00, 1);
INSERT INTO invoices (raised_date, raised_by, customer_id, payment_date, total_amount, is_paid) VALUES ('2021-03-02', 2, 1, '2021-03-03', 454338.00, 1);
DROP TABLE IF EXISTS invoice_lines;
CREATE TABLE invoice_lines(
id int identity(1,1) primary key,
invoice_id int,
product_id int,
unit_count int not null default(0),
unit_price numeric(8,2) not null
);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (2, 1, 49, 7145);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (1, 1, 10, 7145);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (9, 1, 33, 7145);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (4, 2, 9, 9549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (9, 2, 7, 9549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (5, 3, 9, 39549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (4, 2, 10, 8227);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (5, 4, 54, 4883);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (9, 5, 41, 9549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (3, 6, 70, 7347);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (8, 7, 18, 1275);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (2, 1, 27, 7145);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (4, 2, 60, 9549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (9, 5, 13, 9549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (5, 4, 84, 4883);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (4, 6, 67, 7347);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (5, 8, 88, 9549);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (5, 7, 64, 1275);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (7, 7, 20, 1275);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (10, 9, 18, 25241);
INSERT INTO invoice_lines (invoice_id, product_id, unit_count, unit_price) VALUES (6, 3, 1, 39549);
DROP TABLE IF EXISTS products;
CREATE TABLE products(
id int primary key,
item_name varchar(50) not null,
item_description varchar(100),
unit_price numeric(8,2) not null default(0)
);
INSERT INTO products(id, item_name, unit_price) VALUES (1, 'Compactor', 7145);
INSERT INTO products(id, item_name, unit_price) VALUES (2, 'Crawler', 9549);
INSERT INTO products(id, item_name, unit_price) VALUES (3, 'Excavator', 39549);
INSERT INTO products(id, item_name, unit_price) VALUES (4, 'Scraper', 4883);
INSERT INTO products(id, item_name, unit_price) VALUES (5, 'Grader', 9549);
INSERT INTO products(id, item_name, unit_price) VALUES (6, 'Trencher', 7347);
INSERT INTO products(id, item_name, unit_price) VALUES (7, 'Backhoe', 1275);
INSERT INTO products(id, item_name, unit_price) VALUES (8, 'Dragline', 9549);
INSERT INTO products(id, item_name, unit_price) VALUES (9, 'Dump Truck', 25241);
DROP TABLE IF EXISTS product_approvals
CREATE TABLE product_approvals(
id int identity(1,1) primary key,
product_id int,
item_name varchar(50),
item_description varchar(100),
unit_price numeric(8,2) not null default(0),
is_approved bit not null default(0)
)
With our table and data set up as we wanted, let's do those few examples.
DML Trigger Examples
FOR Insert Trigger
FOR Insert Trigger will be useful in cases where we want to set up some simple validation or check the existence of data before performing any operations. This Trigger can be used to run some t-SQL statements before the insert operation in the table. In our case of invoices and invoice_lines table, say we want to implement a rule that once the invoice is settled i.e. paid then new items can't be added to that invoice. The below snippet will help to create a trigger for the same.
CREATE TRIGGER TR_F_I_invoice_lines_validate_invoice_id
ON dbo.invoice_lines
FOR INSERT
AS
IF NOT EXISTS (SELECT * FROM invoices t1 JOIN inserted i ON t1.id = i.invoice_id WHERE t1.is_paid = 0)
BEGIN
RAISERROR('Invoice doesnt exists or is already paid. Hence cant be modified', 16, 1);
ROLLBACK TRANSACTION;
END
GO
INSERT INTO invoice_lines(invoice_id, product_id, unit_count, unit_price) VALUES (2, 1, 1, 7145.00); -- return error
INSERT INTO invoice_lines(invoice_id, product_id, unit_count, unit_price) VALUES (3, 1, 1, 7145.00); -- perfrom the insert operation on invoice_lines table
When we try to insert an invoice line item for invoice_id 2 it returns and rolls back with the error we have mentioned, but in the case of invoice_id = 3, it passes this validation since invoice_id 3 is not paid yet and it also exists in the invoices table.
AFTER Insert Trigger
AFTER Insert Trigger will be useful where we want to execute some T-SQL code after each insertion we have done in that table. Suppose, in our example case, we want to calculate the total amount each time an invoice item is added to the invoice_lines table, one way to do this is to update the invoice total_amount every time a new item is added to the invoice_lines table for that invoice. This is can be done with the 'AFTER Insert trigger' as with the insert operation done in the invoice_line table, we will update the total_amount value in the invoices tables.
CREATE OR ALTER TRIGGER TR_A_I_invoice_lines_update_invoice_total_amount
ON dbo.invoice_lines
AFTER INSERT
AS
BEGIN
UPDATE t1
SET t1.total_amount = t1.total_amount + (t2.unit_price * t2.unit_count)
FROM invoices t1
JOIN inserted t2
ON t1.id = t2.invoice_id;
END
GO
The above code snippet is an AFTER INSERT trigger, wherein we update the total_amount value of the invoice table in case of the new addition of the invoice_items. The below code will show you the example. It updates the total_amount column in the invoices table when that invoice is unpaid, but if it's paid invoice and we try to add new items for it invoice_items table, then it returns with the error from the previous validation trigger we made.
SELECT
*
FROM invoices i
JOIN invoice_lines il
ON i.id = il.invoice_id
WHERE i.id = 3;
INSERT INTO invoice_lines(invoice_id, product_id, unit_count, unit_price) VALUES (3, 4, 1, 4883.00);
INSERT INTO invoice_lines(invoice_id, product_id, unit_count, unit_price) VALUES (3, 2, 1, 9549.00);
SELECT
*
FROM invoices i
JOIN invoice_lines il
ON i.id = il.invoice_id
WHERE i.id = 3;
INSTEAD OF Insert Trigger
Now, we will see a working example for the usage of INSTEAD OF Insert Trigger. It can be helpful in cases where we want to replace the insert operation with some other T-SQL code and execute that code. It mostly is used with views, which is acting as an interface on top of one or multiple table objects for the application server. In our case, we will be using it to change the flow of how the products are added to our catalog i.e. products table. Let's say earlier the company used to directly add the products which they wants to sell directly in the products table. But recently the company has decided to implement an approval process for the products. For every new product being added, it will have to be approved by the company to appear in the products section. In such a scenario we can use INSTEAD OF Insert Trigger to replace the product insert operation in the products table to prdoucts_approvals table. Let's see the trigger for the same.
CREATE OR ALTER TRIGGER TR_IN_instead_products_insert_in_product_approvals
ON dbo.products
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO product_approvals(product_id, item_name, item_description, unit_price)
SELECT i.id, i.item_name, i.item_description, i.unit_price FROM Inserted i;
END
GO
The above trigger definition replaces the insert operation in the products table with the product_approvals table. This way the change in the company product approval process can be altered. Let's try to perform some insert operations.
--select * from products;
--select * from product_approvals;
INSERT INTO products(id, item_name, item_description, unit_price) VALUES (10, 'Pavement Blocks', NULL, 500.78);
--select * from products;
--select * from product_approvals;
With the above example, we can see that the insert operation on the products table is replaced by the product_approvals table. Next, we will be seeing examples of the update operations.
FOR Update Trigger
It is similar to the 'FOR Insert Trigger' we had seen before, but for the update operations. We can have a small validation check or some other T-SQL code executed for each update operation. Earlier in the 'FOR INSERT Trigger' part we created a trigger for validating if the invoice is settled or not and if the invoice is settled, then no new items can be added to that invoice. This trigger won't work in the case of the Update operation of the invoice_lines table. We can test it by updating the invoice_lines table for the invoice_id is 2 and invoice_line_id is 12, even if invoice_id 2 is a paid invoice.
SELECT * FROM invoices WHERE id = 2
SELECT * FROM invoice_lines WHERE invoice_id = 2
UPDATE invoice_lines SET unit_count = unit_count + 1 WHERE invoice_id = 2 and id = 12;
SELECT * FROM invoices WHERE id = 2
SELECT * FROM invoice_lines WHERE invoice_id = 2
Let's prevent this update operation on the invoice_lines table when the invoice is paid and settled. '
CREATE TRIGGER TR_F_U_invoice_lines_validate_for_invoice_id
ON dbo.invoice_lines
FOR UPDATE
AS
IF NOT EXISTS(SELECT * FROM invoices t1 JOIN inserted i ON t1.id = i.invoice_id WHERE t1.is_paid = 0)
BEGIN
RAISERROR('Invoice doesnt exists or is already paid. Hence cant e modified', 16, 1);
ROLLBACK TRANSACTION;
END
GO
After creating FOR Update Trigger on the invoice_lines table, let's again try to update the invoice_lines table where the id is 12 and invoice_id is 2. It will return with an error we mentioned in trigger and rollback since the invoice which we were updating was already a settled invoice, but it successfully updates in the case of invoice id 3 as it is the unpaid invoice. You can try with the below SQL snippet.
SELECT * FROM invoices WHERE id = 2
SELECT * FROM invoice_lines WHERE invoice_id = 2
UPDATE invoice_lines SET unit_count = unit_count + 1 WHERE invoice_id = 2 and id = 12;
SELECT * FROM invoices WHERE id = 2
SELECT * FROM invoice_lines WHERE invoice_id = 2
Next, we will see is AFTER Update Trigger;
AFTER Update Trigger
AFTER Update trigger fires and execute the trigger content when the update operation is completed. Note that if the trigger fails, then the update operation also fails. Earlier in 'FOR Update Trigger', we made a trigger to block the updates of the invoices which are already settled, and not of unpaid invoices. Since the unpaid invoices represent that the customer is still in process of shortlisting the items he wants to purchase, we should be able to show the updated total amount of the invoice, every time he updates his invoice items.
CREATE OR ALTER TRIGGER TR_A_U_invoice_lines_update_invoice_total_amount
ON dbo.invoice_lines
AFTER UPDATE
AS
BEGIN
UPDATE t1
SET t1.total_amount = t1.total_amount + ((t2.unit_count - t3.unit_count) * t2.unit_price)
FROM invoices t1
JOIN inserted t2
ON t1.id = t2.invoice_id
JOIN deleted t3
ON t2.id = t3.id
;
END
GO
With the trigger above, every time the invoice_lines table is modified or altered for the invoice id which is not been paid yet, the total amount column in the invoices table will be updated to reflect the new amount.
SELECT * FROM invoices WHERE id = 3;
SELECT * FROM invoice_lines WHERE invoice_id = 3;
UPDATE invoice_lines SET unit_count = unit_count + 1 WHERE invoice_id = 3 and id = 10;
SELECT * FROM invoices WHERE id = 3;
SELECT * FROM invoice_lines WHERE invoice_id = 3;
With every update operation in the invoice_lines table, we update the total amount value in the invoices table, having a column named total_amount.
INSTEAD OF Update Trigger
Similar to INSTEAD OF Insert Trigger, this trigger can be used for a view referencing two or more tables. INSTEAD OF Update Trigger can be used when we want to perform something else instead of the update operation on that table. Let's continue with our example. Earlier we implemented a product_approvals table wherein any new products will have to be approved first by the company before making them available for sale. Similarly, now the company wants to add another flow to it. The change in the price of the product should also be approved by the company before begin passed down to new customers. Let's make a Trigger for that case.
CREATE OR ALTER TRIGGER TR_IN_products_price_update_needs_product_approvals
ON dbo.products
INSTEAD OF UPDATE
AS
BEGIN
INSERT INTO product_approvals(product_id, item_name, item_description, unit_price)
SELECT id, item_name, item_description, unit_price FROM inserted
END
GO
When someone tries to update the price of the product, the trigger will instead execute its content. It will insert data in the product_approvals table for that product_id with new column values and set the is_approved flag 0. So it will be reflected on the approvals page and once approved then the changes can be reflected in the products table.
UPDATE products SET unit_price = 7545 WHERE id = 1;
The above update query operation will instead execute the trigger content we created replacing it with inserting the modified row data in the product_approvals table. Now we will be exploring triggers for the DML Delete operation.
FOR Delete Trigger
Similar to FOR Insert Trigger and FOR Update Trigger, FOR delete trigger can be used to perform simple validation before the delete operation, or for some other code execution before the delete operation. Here let's say, before deleting the product from the products table, we have to make sure that there are no pending invoices that have those products in its invoice item list. If the product is in the list then, we have to return an error with a reason for the failed delete operation.
CREATE OR ALTER TRIGGER TR_F_D_product_delete_validate_with_invoice_lines
ON dbo.products
FOR DELETE
AS
IF EXISTS(
SELECT *
FROM invoice_lines il
JOIN invoices i
ON i.id = il.invoice_id
JOIN deleted d
ON il.product_id = d.id WHERE i.is_paid = 0)
BEGIN
RAISERROR('Product is added in unsettled invoice bill. cant delete at this instance. Try later.', 16, 1);
ROLLBACK TRANSACTION;
END
GO
DELETE FROM products WHERE id = 6; -- return error
DELETE FROM products WHERE id = 1; -- success
This trigger prevents the deletion of the product which is in the invoice and hasn't yet been settled. Similarly, you can also use FOR DELETE Trigger to validate the delete operation of the already paid invoice.
AFTER Delete Trigger
The AFTER Delete Trigger is used to perform operations when the delete operation is executed. Continuing with our example, let's write a trigger for the case where the invoice item of an unpaid invoice is deleted, then the total amount of that invoice should get updated as well.
CREATE OR ALTER TRIGGER TR_A_D_invoice_lines_update_invoice_total_amount
ON dbo.invoice_lines
AFTER DELETE
AS
BEGIN
-- select * from deleted;
UPDATE t1
SET t1.total_amount = t1.total_amount - (t2.unit_price * t2.unit_count)
FROM invoices t1
JOIN deleted t2
ON t1.id = t2.invoice_id;
END
GO
This trigger updates the total amount of the invoice once the invoice item is deleted. With this, we see the usage of the AFTER Delete trigger. Run the below SQL code to see them working.
SELECT * FROM invoices WHERE id = 3;
SELECT * FROM invoice_lines WHERE invoice_id = 3;
DELETE FROM invoice_lines WHERE id = 23;
SELECT * FROM invoices WHERE id = 3;
SELECT * FROM invoice_lines WHERE invoice_id = 3;
INSTEAD OF Delete Trigger
Continuing our example further, we want to prevent the delete operation on invoices that are already settled and instead return an error. This works similarly to the INSTEAD OF Insert Trigger and INSTEAD OF Update Trigger. Below is the Trigger definition, which prevents the delete operation and returns an error.
CREATE OR ALTER TRIGGER TR_IN_paid_invoice_cant_be_deleted
ON dbo.invoices
INSTEAD OF DELETE
AS
IF EXISTS(
SELECT *
FROM invoices i
JOIN deleted
ON i.id = deleted.id
WHERE i.is_paid = 1
)
BEGIN
RAISERROR('You cant delete an paid invoice.', 16, 1);
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
DELETE
i
FROM invoices i
JOIN deleted d
ON i.id = d.id
WHERE i.is_paid = 0;
END
GO
In the trigger defined above when the delete operation is executed for the invoices table, we just verify if the invoice we are deleting is already a paid invoice or not. If it's a paid invoice then we prevented the delete operation and instead returned with the error, but if the invoice is unpaid then we can delete that invoice. The below statements help us to verify if the trigger created earlier is working as expected or not.
DELETE FROM invoices WHERE id = 1;
DELETE FROM invoices where id = 7;
-- select * from invoices
This completes our little post regarding the Trigger and its example for all the DML events. In the upcoming post, related to Trigger, we will see how Trigger can be used for multiple or combined DML events like using Trigger to Log Table Data Changes, Nested and Recursive Triggers and DDL Triggers with Events to track database alteration and its example trying to log database changes.