Microsoft SQL Server

Database triggers in Microsoft SQL Server are special types of stored procedures that automatically execute in response to certain events on a specified table or view. They are often used for tasks such as enforcing business rules, maintaining audit trails, and synchronizing tables. Understanding how to create and use triggers is essential for any database programmer, as they can help automate tasks and ensure data integrity.

1. Types of Triggers
   • DML Triggers: These are fired in response to Data Manipulation Language events such as INSERT, UPDATE, or DELETE.
     • AFTER Triggers: Execute after the triggering event.
     • INSTEAD OF Triggers: Execute in place of the triggering event.
   • DDL Triggers: These are fired in response to Data Definition Language events like CREATE, ALTER, or DROP.
   • LOGON Triggers: These execute in response to a logon event.
2. Creating DML Triggers
   • To create a trigger, you use the CREATE TRIGGER statement. Here are examples of different types of DML triggers.

   a. AFTER Trigger Example

   CREATE TRIGGER trg_AfterInsert ON Products
   AFTER INSERT
   AS
   BEGIN
       PRINT 'A new product has been added.'
   END
   

   b. INSTEAD OF Trigger Example

   CREATE TRIGGER trg_InsteadOfDelete ON Products
   INSTEAD OF DELETE
   AS
   BEGIN
       PRINT 'Delete operation is not allowed on Products.'
   END
   

3. Creating DDL Triggers
   • DDL triggers are created in a similar fashion but target schema or database events.
   DDL Trigger Example

   CREATE TRIGGER trg_PreventDrop ON DATABASE
   FOR DROP_TABLE
   AS
   BEGIN
       RAISERROR('Dropping tables is not allowed.', 16, 1);
       ROLLBACK;
   END
   

4. Best Practices for Using Triggers
   • Document Your Triggers: Always include comments explaining the purpose of the trigger.
   • Keep Logic Simple: Limit the complexity of trigger logic to avoid performance degradation.
   • Limit Usage: Use triggers sparingly; consider whether stored procedures or application logic would be more appropriate.
   • Avoid Side Effects: Ensure that triggers do not have unintended consequences, such as cascading updates or inserts.
   • Test Thoroughly: Always test triggers under various scenarios to confirm they behave as intended.
5. Testing Your Triggers
   • After creating a trigger, always test it with various scenarios to ensure it behaves as expected.
   • Use INSERT, UPDATE, and DELETE statements to see how the triggers respond.

By following these guidelines and examples, beginners can start effectively using triggers in Microsoft SQL Server, enhancing their database programming skills.

INSERT Trigger:

An INSERT trigger to automatically log new employee records into an audit table.

CREATE TRIGGER AfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeAudit (Action, EmployeeID, FirstName, LastName, Salary, Timestamp)
    SELECT 'New employee added', EmployeeID, FirstName, LastName, Salary, GETDATE()
    FROM inserted;
END;

UPDATE Trigger:

An UPDATE trigger to update the last modified timestamp for employees.

CREATE TRIGGER AfterUpdateEmployee
ON Employees
AFTER UPDATE
AS
BEGIN
    UPDATE Employees
    SET LastModified = GETDATE()
    FROM Employees e
    INNER JOIN inserted i
    ON e.EmployeeID = i.EmployeeID;
END;

DELETE Trigger:

A DELETE trigger to archive deleted employee records.

CREATE TRIGGER AfterDeleteEmployee
ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO ArchivedEmployees (EmployeeID, FirstName, LastName, Salary, DeletedTimestamp)
    SELECT EmployeeID, FirstName, LastName, Salary, GETDATE()
    FROM deleted;
END;