Triggers in SQL Server are special types of stored procedures that automatically execute or “fire” when certain events occur in the database.
Types of Triggers
DML Triggers
These are triggers that fire in response to data manipulation events such as INSERT, UPDATE, or DELETE operations on a table or view.
- AFTER Triggers: Execute after the associated DML operation is completed.
- INSTEAD OF Triggers: Execute in place of the associated DML operation.
DDL Triggers
These fire in response to data definition language (DDL) events such as CREATE, ALTER, or DROP statements.
Real-Time Use Case Example
Scenario. Imagine a retail company that wants to maintain an audit trail for all changes made to the Orders table. Every time an order is inserted, updated, or deleted, the company wants to log the details of the operation in an OrderAudit table.
Setting Up the Example
First, let’s create the Orders table and the OrderAudit table.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME,
Amount DECIMAL(10, 2)
);
CREATE TABLE OrderAudit (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
OrderID INT,
Operation VARCHAR(10),
OldCustomerID INT,
NewCustomerID INT,
OldOrderDate DATETIME,
NewOrderDate DATETIME,
OldAmount DECIMAL(10, 2),
NewAmount DECIMAL(10, 2),
ChangeDate DATETIME DEFAULT GETDATE()
);
Creating Triggers
AFTER INSERT Trigger
CREATE TRIGGER trg_AfterInsert_Orders
ON Orders
AFTER INSERT
AS
BEGIN
INSERT INTO OrderAudit (OrderID, Operation, NewCustomerID, NewOrderDate, NewAmount)
SELECT OrderID, 'INSERT', CustomerID, OrderDate, Amount
FROM inserted;
END;
-- Insert a new order
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)
VALUES (1, 101, '2024-01-01', 100.00);
-- Check audit logs
select * from OrderAudit
Result
We can see we have a record inserted into the audit log table, and when any record is inserted into the order table, we will know what operation was performed and at what time it was executed.
AFTER INSERT Trigger: When a new order is inserted into the Orders table, the trg_AfterInsert_Orders trigger fires. It captures the new order details from the inserted pseudo-table and inserts a corresponding record into the OrderAudit table with the operation type 'INSERT'.
2. AFTER UPDATE Trigger
CREATE TRIGGER trg_AfterUpdate_Orders
ON Orders
AFTER UPDATE
AS
BEGIN
INSERT INTO OrderAudit (OrderID, Operation, OldCustomerID, NewCustomerID, OldOrderDate, NewOrderDate, OldAmount, NewAmount)
SELECT
i.OrderID,
'UPDATE',
d.CustomerID,
i.CustomerID,
d.OrderDate,
i.OrderDate,
d.Amount,
i.Amount
FROM inserted i
INNER JOIN deleted d ON i.OrderID = d.OrderID;
END;
-- Update the order
UPDATE Orders
SET Amount = 120.00
WHERE OrderID = 1;
-- Audit log
SELECT * FROM OrderAudit;
Result
we can see at this time, we updated the record, and we can see details of updated records, this is very helpful in case when we want to track how many time a order has been updated etc.
AFTER UPDATE Trigger: When an existing order is updated, the trg_AfterUpdate_Orders trigger fires. It captures both the old and new values by joining the inserted and deleted pseudo-tables and inserts a record into the OrderAudit table with the operation type 'UPDATE'.
3. AFTER DELETE Trigger
CREATE TRIGGER trg_AfterDelete_Orders
ON Orders
AFTER DELETE
AS
BEGIN
INSERT INTO OrderAudit (OrderID, Operation, OldCustomerID, OldOrderDate, OldAmount)
SELECT
OrderID,
'DELETE',
CustomerID,
OrderDate,
Amount
FROM deleted;
END;
-- Delete the order
DELETE FROM Orders
WHERE OrderID = 1;
-- Reading audit table
SELECT * FROM OrderAudit;
Result. We can see that order data has been deleted, and we can check logs to see what time it was deleted.
AFTER DELETE Trigger: When an order is deleted, the trg_AfterDelete_Orders trigger fires. It captures the details of the deleted order from the deleted pseudo-table and inserts a corresponding record into the OrderAudit table with the operation type 'DELETE'.
Dropping a Trigger
Just use DROP triggername.
Instead of Trigger
Let’s consider a table of Employees where we want to restrict the insertion of records such that the Salary must be above a certain threshold (e.g., $30,000). If the Salary is below this threshold, the insertion should be rejected.
We can use it instead of Trigger in this case.
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100),
Position VARCHAR(50),
Salary DECIMAL(10, 2)
);
CREATE TRIGGER trg_InsteadOfInsert_Employees
ON Employees
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE Salary < 30000)
BEGIN
-- Raise an error and roll back the transaction if the salary is below the threshold
RAISERROR ('Salary must be at least $30,000', 16, 1);
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
-- Perform the actual insert if the condition is met
INSERT INTO Employees (Name, Position, Salary)
SELECT Name, Position, Salary
FROM inserted;
END
END;
--
--less than 90000
-- This insertion should fail
INSERT INTO Employees (Name, Position, Salary)
VALUES ('peter scott', 'Developer', 89999);
Explanation
- The trigger intercepts the insert operation and checks if any of the inserted rows have a Salary below $90,000.
- If any rows do not meet the condition, an error is raised, and the transaction is rolled back, preventing the insertion.
- If all rows meet the condition, the insertion proceeds by inserting the rows into the Employees table.
Thanks. We have learned triggers.
HostForLIFEASP.NET SQL Server 2022 Hosting
