SQL Server Triggers Examples - Practical Tutorial

What Are SQL Server Triggers?

A trigger is a database object that fires automatically in response to INSERT, UPDATE, or DELETE operations on a table or view.

Triggers can be defined as AFTER triggers, which execute after the triggering statement, or INSTEAD OF triggers, which execute in place of the triggering statement.

• AFTER triggers run after the data modification completes successfully.
• INSTEAD OF triggers execute instead of the data modification, allowing custom logic.
• Triggers can access special tables called inserted and deleted to examine data changes.

AFTER Trigger Example: Auditing Data Changes

AFTER triggers are commonly used to track changes in data for auditing purposes.

Below is an example of an AFTER INSERT trigger that logs new employee records into an audit table.

INSTEAD OF Trigger Example: Enforcing Business Rules

INSTEAD OF triggers allow you to override the default behavior of data modification statements.

They are useful for enforcing complex business rules or modifying data before it is saved.

Using Inserted and Deleted Tables in Triggers

SQL Server provides two special tables inside triggers: inserted and deleted.

These tables hold the new and old versions of the rows affected by the triggering statement.

• The inserted table contains the new rows for INSERT and UPDATE operations.
• The deleted table contains the old rows for DELETE and UPDATE operations.
• You can join these tables to compare changes and enforce rules.