In SQL Server, triggers are a type of stored procedure that automatically execute in response to specific data manipulation events, such as insert, update, or delete operations on a table. Triggers can be used to enforce data integrity rules, audit changes to the data, or perform complex business logic.
There are two types of triggers in SQL Server:
DML Triggers:
These triggers fire in response to data manipulation language (DML) statements, such as INSERT, UPDATE, and DELETE. DML triggers can be defined to execute either before or after the DML statement.
DDL Triggers:
These triggers fire in response to data definition language (DDL) statements, such as CREATE, ALTER, and DROP. DDL triggers can be defined to execute either before or after the DDL statement.
DML triggers can be further classified into two types based on when they fire:
- After Triggers: These triggers execute after the DML statement has completed, but before any constraints or referential integrity rules are enforced.
- Instead of Triggers: These triggers execute instead of the DML statement. They allow you to modify the data or perform custom business logic before the data is inserted, updated, or deleted.
Triggers can be defined at the table level or at the database level. When a trigger is defined at the table level, it only fires in response to data manipulation events on that table. When a trigger is defined at the database level, it fires in response to data manipulation events on any table in the database.
Here are a few examples of triggers in SQL Server:
Implementation as Audit Trigger: This trigger can be used to audit changes to a table. For example, suppose you have a table named "Employee" and you want to track changes to the "Salary" column. You can create a trigger that fires after an update to the "Salary" column and inserts a record into an audit table.
Here's an example of the trigger:
CREATE TRIGGER Audit_Employee_Salary
ON Employee
AFTER UPDATE
AS
BEGIN
INSERT INTO Employee_Audit (EmployeeID, OldSalary, NewSalary, ChangeDate)
SELECT i.EmployeeID, d.Salary, i.Salary, GETDATE()
FROM inserted i
INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID
END
Implementation as Constraint Trigger: This trigger can be used to enforce data integrity rules. For example, suppose you have a table named "Orders" and you want to ensure that the total amount of an order cannot exceed the customer's credit limit. You can create a trigger that fires before an insert or update to the "Orders" table and checks the customer's credit limit.
Here's an example of the trigger:
CREATE TRIGGER Orders_CreditLimit
ON Orders
INSTEAD OF INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT *
FROM inserted i
INNER JOIN Customers c ON i.CustomerID = c.CustomerID
WHERE i.TotalAmount > c.CreditLimit
)
BEGIN
RAISERROR ('Total amount of the order exceeds the customer''s credit limit.', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM inserted
END
Note that these are just a few above examples of the types of triggers that can be created in SQL Server. The possibilities are endless, and triggers can be used to perform a wide range of tasks depending on your specific needs.
