Explain the significance of INSERTED & DELETED tables with respect to triggers
- User
Explain the significance of INSERTED & DELETED tables with respect to triggers
- Assistant
In SQL Server,
INSERTEDandDELETEDare special logical tables used within triggers to capture the state of data before and after Data Manipulation Language (DML) operations such asINSERT,UPDATE, andDELETE. These tables are pivotal for writing effective triggers that respond appropriately to data changes. Here's a detailed explanation of their significance:Understanding
INSERTEDandDELETEDTables-
INSERTEDTable:- Purpose: Holds the new rows that are being inserted or the new state of rows that are being updated.
- Usage Scenarios:
INSERTOperations: Contains the rows that are about to be inserted into the target table.UPDATEOperations: Contains the new values of the rows after the update.
-
DELETEDTable:- Purpose: Holds the old rows that are being deleted or the old state of rows that are being updated.
- Usage Scenarios:
DELETEOperations: Contains the rows that are about to be deleted from the target table.UPDATEOperations: Contains the original values of the rows before the update.
Trigger Types Using
INSERTEDandDELETED- AFTER Triggers: Execute after the DML operation has occurred. They can access both
INSERTEDandDELETEDto determine what changes have been made. - INSTEAD OF Triggers: Execute in place of the DML operation. They can use
INSERTEDandDELETEDto decide how to handle the operation.
Practical Examples
Let's consider a table named
Employeeswith columnsEmployeeID,Name, andSalary.-
INSERTOperation: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
INSERTEDtable.
- Behavior: When a new employee is added, the trigger can access the new employee's details from the
-
DELETEOperation: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
DELETEDtable.
- Behavior: When an employee is removed, the trigger can access the removed employee's details from the
-
UPDATEOperation: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.
- Behavior: When an employee's salary is updated, the trigger can compare the old salary (
Key Points to Remember
-
Multi-row Operations: Both
INSERTEDandDELETEDcan 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:
INSERTEDandDELETEDare 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
INSERTEDandDELETEDtables 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. -