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.

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.

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.

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.

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.

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.

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.

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.

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.