(1) SQL Server: After TRIGGER. How to create TRIGGER for AFTER (insert, update and delete).
Introduction
Creating a trigger on a small table to capture insert, update, and delete events.
SQL code available on GitHub.
Setting Up Tables
Create Table: Item
Fields:
Item ID (Integer, Primary Key)
Item Type (Not Null)
Item Name (Not Null)
Price (Not Null)
Create Table: Item Audit
Fields:
Item Audit ID (Identity Column, Primary Key)
Item ID
Item Type
Item Name
Price
Operation (To track the type of change)
Create Date (Automatically set from system)
User ID (Automatically set from system user)
Primary Key
Item Audit ID is the primary key for the audit table.
Implementing a Check Constraint
Restricting Operation Values
Alter table: (ALTER TABLE item_audit ADD CONSTRAINT check_operation CHECK (operation IN ('insert', 'update', 'delete'))).
Demonstration of how the check constraint works.
Valid inserts: "insert", "UPDATE", etc.
Invalid insert (e.g., "try ins") fails due to check constraint.
Understanding Triggers
Trigger Overview
Three triggers for the item table:
Insert Trigger: Executes after an insert command.
Update Trigger: Executes after an update command.
Delete Trigger: Executes after a delete command.
Execution Timing
Triggers execute after the respective SQL command has completed.
Trigger Functionality
Insert Trigger
After inserting into the item table, it records the event in item audit.
Update Trigger
After updating an item, it captures the previous data from the "deleted" temporary table before the change.
Delete Trigger
After deleting an item, it logs the record of the deleted item into item audit.
Consolidating Triggers into One
Unified Trigger: TR item audit to support insert, update, and delete in one trigger.
Setup
Set NOCOUNT ON (to suppress unnecessary messages).
Declare a variable for operation type.
Internal Temporary Tables
Uses two internal tables: "inserted" (for future state) and "deleted" (for original state).
Logic Flow
Exists check for "inserted" signifies an update; otherwise, it's a delete.
If not in "deleted", it’s an insert.
Operations
Sets the operation variable appropriately based on inserts, updates, or deletes and performs respective audit insert.
Conclusion
Review
Triggers are associated with only two internal temporary tables: inserted and deleted.