SQL Server Triggers are special types of stored procedures that automatically execute in response to certain events on a table or view. They can be used for tasks such as enforcing business rules, maintaining audit trails, and synchronizing tables.

Types of Triggers

1. AFTER Triggers:

   • Execute after an INSERT, UPDATE, or DELETE operation.

   • Commonly used for enforcing referential integrity or auditing changes.

   • Example:

   sql

   CREATE TRIGGER trgAfterInsert
   ON Employees
   AFTER INSERT
   AS
   BEGIN
       INSERT INTO EmployeeAudit (EmployeeID, Action, ActionDate)
       SELECT EmployeeID, 'INSERT', GETDATE()
       FROM inserted;
   END;
   

2. INSTEAD OF Triggers:

   • Execute in place of an INSERT, UPDATE, or DELETE operation.

   • Useful for views that need to handle data modifications or complex validation logic.

   • Example:

   sql

   CREATE TRIGGER trgInsteadOfInsert
   ON EmployeeView
   INSTEAD OF INSERT
   AS
   BEGIN
       INSERT INTO Employees (FirstName, LastName)
       SELECT FirstName, LastName
       FROM inserted;
   END;
   

Key Concepts

• Inserted and Deleted Tables: Triggers use these special tables to access the data before and after the triggering event.

  • inserted table holds a copy of the affected rows after an INSERT or UPDATE operation.

  • deleted table holds a copy of the affected rows before a DELETE or UPDATE operation.

Example of an UPDATE Trigger

CREATE TRIGGER trgAfterUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
    DECLARE @OldSalary DECIMAL, @NewSalary DECIMAL;

    SELECT @OldSalary = d.Salary, @NewSalary = i.Salary
    FROM inserted i
    JOIN deleted d ON i.EmployeeID = d.EmployeeID;

    IF @OldSalary <> @NewSalary
    BEGIN
        INSERT INTO EmployeeSalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
        SELECT i.EmployeeID, d.Salary, i.Salary, GETDATE()
        FROM inserted i
        JOIN deleted d ON i.EmployeeID = d.EmployeeID;
    END
END;

Considerations

• Performance: Triggers can impact performance, especially if they contain complex logic or affect many rows. Use them judiciously.

• Recursive Triggers: By default, recursive triggers are disabled. Be cautious when enabling them to avoid infinite loops.

• Error Handling: Implement proper error handling within triggers to manage exceptions effectively.

Managing Triggers

• Enable/Disable Triggers:

  sql

  -- Disable trigger
  DISABLE TRIGGER trgAfterInsert ON Employees;
  
  -- Enable trigger
  ENABLE TRIGGER trgAfterInsert ON Employees;
  

• Drop Triggers:

  sql

  DROP TRIGGER trgAfterInsert ON Employees;
  

Triggers are powerful tools for maintaining data integrity and enforcing business rules. If you have specific scenarios or need more examples, feel free to ask!