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
1. 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.
2. 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.
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
1. 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 record inserted into audit log table and when any record inserted into order table we will know what operation performed and at what time it 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 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 order data has been deleted and we can check logs at what time its 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'.
Instead of Trigger
Let's consider a table Employees where we want to restrict the insertion of records such that the Salary must be above a certain threshold (e.g., $90,000). If the Salary is below this threshold, the insertion should be rejected.
We can use 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 ('devesh omar', 'Developer', 89999);
Explanation
Droping a Trigger
Just use DROP triggername
Conclusion
We have understood triggers and its practical implementation
Happy Coding and happy learnings.
Copyrights © 2024 letsupdateskills All rights reserved