Skip to content
TheChatArchiveThe Chat Archive
o1-mini-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

Explain the significance of INSERTED & DELETED tables with respect to triggers

  1. User

    Explain the significance of INSERTED & DELETED tables with respect to triggers

  2. Assistant

    In SQL Server, INSERTED and DELETED are special logical tables used within triggers to capture the state of data before and after Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE. These tables are pivotal for writing effective triggers that respond appropriately to data changes. Here's a detailed explanation of their significance:

    Understanding INSERTED and DELETED Tables

    1. INSERTED Table:

      • Purpose: Holds the new rows that are being inserted or the new state of rows that are being updated.
      • Usage Scenarios:
        • INSERT Operations: Contains the rows that are about to be inserted into the target table.
        • UPDATE Operations: Contains the new values of the rows after the update.
    2. DELETED Table:

      • Purpose: Holds the old rows that are being deleted or the old state of rows that are being updated.
      • Usage Scenarios:
        • DELETE Operations: Contains the rows that are about to be deleted from the target table.
        • UPDATE Operations: Contains the original values of the rows before the update.

    Trigger Types Using INSERTED and DELETED

    • AFTER Triggers: Execute after the DML operation has occurred. They can access both INSERTED and DELETED to determine what changes have been made.
    • INSTEAD OF Triggers: Execute in place of the DML operation. They can use INSERTED and DELETED to decide how to handle the operation.

    Practical Examples

    Let's consider a table named Employees with columns EmployeeID, Name, and Salary.

    1. INSERT Operation:

      CREATE TRIGGER trgAfterInsert ON Employees
      AFTER INSERT
      AS
      BEGIN
          -- Access new rows from INSERTED
          SELECT * FROM INSERTED
          -- Example: Log the inserted data
      END
      
      • Behavior: When a new employee is added, the trigger can access the new employee's details from the INSERTED table.
    2. DELETE Operation:

      CREATE TRIGGER trgAfterDelete ON Employees
      AFTER DELETE
      AS
      BEGIN
          -- Access deleted rows from DELETED
          SELECT * FROM DELETED
          -- Example: Log the deleted data
      END
      
      • Behavior: When an employee is removed, the trigger can access the removed employee's details from the DELETED table.
    3. UPDATE Operation:

      CREATE TRIGGER trgAfterUpdate ON Employees
      AFTER UPDATE
      AS
      BEGIN
          -- Access old data from DELETED and new data from INSERTED
          SELECT d.EmployeeID, d.Salary AS OldSalary, i.Salary AS NewSalary
          FROM DELETED d
          INNER JOIN INSERTED i ON d.EmployeeID = i.EmployeeID
          -- Example: Track salary changes
      END
      
      • Behavior: When an employee's salary is updated, the trigger can compare the old salary (DELETED) with the new salary (INSERTED) to, for example, log the changes or enforce business rules.

    Key Points to Remember

    • Multi-row Operations: Both INSERTED and DELETED can contain multiple rows, especially in bulk operations. Triggers should be designed to handle such scenarios without assuming only a single row is affected.

    • Read-Only Nature: These logical tables are read-only within the trigger. You cannot perform DML operations directly on them.

    • Scope Limitation: INSERTED and DELETED are only accessible within the context of the trigger in which they're used.

    Use Cases and Benefits

    • Auditing and Logging: Track changes to data over time by logging inserts, updates, and deletes.

    • Enforcing Business Rules: Automatically enforce complex business logic that depends on the data being inserted or modified.

    • Synchronized Updates: Maintain consistency across related tables by propagating changes as needed.

    Conclusion

    The INSERTED and DELETED tables are fundamental components of SQL Server triggers, providing a powerful mechanism to access and manipulate data changes in response to DML operations. By leveraging these logical tables, developers can create sophisticated triggers that enhance data integrity, enforce business logic, and maintain comprehensive audit trails.