Summary of "After update trigger Part 44"

Video Summary: After Update Trigger - Part 44 (SQL Server)

Storyline / Context

This video is part 44 of a SQL Server tutorial series by Preim Technologies, focusing on after update triggers. Earlier videos (part 42) covered after insert and after delete triggers, as well as the types of triggers in SQL Server: DML, DDL, and logon triggers. This session explains how after triggers work, specifically after update triggers, and demonstrates their practical use.

Key Concepts Discussed

Types of Triggers:
- DML triggers respond to data manipulation events (INSERT, UPDATE, DELETE).
- DML triggers are classified as:
  - After triggers: Fire after the event.
  - Instead of triggers: Fire instead of the event.
Inserted and Deleted Tables:
- Special tables used in triggers.
- For an update trigger:
  - Deleted table holds the old data (before update).
  - Inserted table holds the new data (after update).

Gameplay Highlights / Demonstration

Created an after update trigger on a table [TBL_employee](https://www.amazon.com/dp/1943872570?tag=dtdgstoreid08-20) with columns: ID, Name, Salary, Gender, DepartmentID.
The trigger selects and displays data from inserted and deleted tables after an update operation.
Example update performed:
- Change employee name from Jane to James.
- Change salary and gender.
Observations:
- Deleted table contains old data.
- Inserted table contains new data.

Step-by-Step Guide to Creating an Audit Trigger for Updates

Goal: Audit changes made to employee data and log the changes into the TBL_employee_audit table.

Audit Table Structure: - Has an identity primary key. - Contains an audit_data column to store audit strings.

Trigger Logic:

Create or alter an after update trigger on [TBL_employee](https://www.amazon.com/dp/1943872570?tag=dtdgstoreid08-20).
Declare variables to hold old and new values for each column (Name, Salary, Gender, DepartmentID).
Use a temporary table to hold rows from the inserted table.
Use a loop to process each updated row individually (to handle multiple updates in one statement).
For each row:
- Retrieve new values from the temporary table.
- Retrieve old values from the deleted table using the same ID.
- Compare old and new values for each column.
- Build an audit string describing the changes, e.g., "Employee with ID=2 changed name from My to MyKey".
- Insert the audit string into the audit table.
- Delete the processed row from the temporary table to avoid infinite loops.
Commit the trigger.

Key Tips / Strategies

Use both inserted and deleted tables to compare old and new data during updates.
Use a temporary table and loop to handle multiple rows updated in one statement.
Build audit strings dynamically based on which columns changed.
Always delete processed rows from the temporary table inside the loop to prevent infinite looping.
Use meaningful trigger names with a TR_ prefix.
Cast data types properly when concatenating strings (e.g., integer to nvarchar).