Contents
Triggers
Triggers are special SQL procedures that automatically execute or fire when a specific database event occurs, such as insert, update, or delete operations. They are used to enforce business rules, maintain data integrity, and audit data changes.
1. Creating Triggers
Definition: Triggers are blocks of SQL code that automatically execute in response to certain events on a particular table or view in a database.
Example:
CREATE TRIGGER trgAfterInsert
ON Customers
AFTER INSERT
AS
BEGIN
INSERT INTO AuditLog (CustomerID, Action)
SELECT CustomerID, 'INSERT' FROM inserted;
END;
This SQL statement creates a trigger named trgAfterInsert that logs every insert operation on the Customers table by inserting relevant information into an AuditLog table.
1.2 Types of Triggers
1.2.1 BEFORE Triggers
Definition: BEFORE triggers execute before the intended insert, update, or delete operation is completed on the database table.
Example:
CREATE TRIGGER trgBeforeInsert
BEFORE INSERT ON Customers
FOR EACH ROW
BEGIN
SET NEW.CreatedAt = NOW();
END;
This trigger, trgBeforeInsert, automatically sets the CreatedAt timestamp just before a new record is inserted into the Customers table, ensuring that the timestamp is accurately recorded at the time of insertion.
1.2.2 AFTER Triggers
Definition: AFTER triggers execute after the associated insert, update, or delete operation has been completed on the table.
Example:
CREATE TRIGGER trgAfterUpdate
ON Customers
AFTER UPDATE
AS
BEGIN
INSERT INTO AuditLog (CustomerID, Action)
SELECT CustomerID, 'UPDATE' FROM inserted;
END;
This trigger, trgAfterUpdate, logs updates to the Customers table by recording them in an AuditLog table after any record is updated, thus helping to maintain an audit trail.
1.2.3 INSTEAD OF Triggers
Definition: INSTEAD OF triggers execute in place of an insert, update, or delete operation. They are typically used with views which do not support direct updates.
Example:
CREATE TRIGGER trgInsteadOfDelete
ON Customers
INSTEAD OF DELETE
AS
BEGIN
INSERT INTO DeletedCustomers (CustomerID, Name, Address)
SELECT CustomerID, Name, Address FROM deleted;
END;
This trigger, trgInsteadOfDelete, captures delete operations intended for the Customers table and instead inserts the deleted data into a DeletedCustomers table, preserving the original data before deletion.
1.3 Dropping Triggers
Definition: Dropping a trigger involves removing it from the database, effectively stopping it from executing its associated actions.
Example:
DROP TRIGGER trgAfterInsert;
This SQL statement removes the trigger named trgAfterInsert from the database, which means that subsequent inserts into the Customers table will no longer be logged in the AuditLog table.
Practical Scenario: Using Triggers for Auditing
Step 1: Creating an Audit Log Table
CREATE TABLE AuditLog (
LogID int PRIMARY KEY IDENTITY(1,1),
CustomerID int,
Action varchar(50),
ActionTime datetime DEFAULT GETDATE()
);
This table will store logs for actions performed on the Customers table.
Step 2: Creating AFTER INSERT Trigger
CREATE TRIGGER trgLogInsert
ON Customers
AFTER INSERT
AS
BEGIN
INSERT INTO AuditLog (CustomerID, Action)
SELECT CustomerID, 'INSERT' FROM inserted;
END;
This trigger logs each insert action on the Customers table by adding a record to the AuditLog table.
Step 3: Inserting Data into Customers Table
INSERT INTO Customers (CustomerID, Name, Address)
VALUES (101, 'John Doe', '123 Elm St');
After this insert, the trgLogInsert trigger fires and logs the action in the AuditLog table.
Step 4: Reviewing Audit Logs
SELECT * FROM AuditLog;
This query retrieves all logs, showing the actions performed on the Customers table, including the recent insert.
By utilizing triggers, businesses can automate crucial tasks like data integrity enforcement, business rule applications, and change logging, making database systems more robust and reliable.