SQL Server Triggers
Learn what SQL Server Triggers are, why they are used, and how they automatically execute when database events occur.
Triggers Introduction
A Trigger is a special type of stored procedure that automatically executes when a specific event occurs in a SQL Server database.
Unlike a normal stored procedure, a trigger is not executed
directly using the EXEC statement.
SQL Server automatically executes the trigger when its
associated database event occurs.
Triggers are commonly used to maintain data integrity, keep audit information, and automatically perform additional operations when data is inserted, updated, or deleted.
What is a Trigger?
A trigger is a database object that is automatically executed in response to a specific event on a table or other database object.
For example, when a new student record is inserted into the Students table, an AFTER INSERT trigger can automatically execute another SQL statement.
EXEC TriggerName.
Why are Triggers Used?
Triggers are useful when an operation should happen automatically after or instead of a database event.
- Automatically maintain related data.
- Maintain audit information.
- Track INSERT, UPDATE and DELETE operations.
- Maintain data consistency.
- Perform automatic actions when table data changes.
Common Trigger Events
Triggers can be associated with different database operations.
| Event | Description |
|---|---|
| INSERT | Executes when a new record is inserted into a table. |
| UPDATE | Executes when an existing record is modified. |
| DELETE | Executes when a record is deleted from a table. |
Types of Triggers Covered
In this section we will learn the following triggers:
1. AFTER INSERT Trigger
Executes after a new record has been successfully inserted.
2. AFTER UPDATE Trigger
Executes after an existing record has been updated.
3. AFTER DELETE Trigger
Executes after a record has been deleted.
4. INSTEAD OF Trigger
Executes instead of the original INSERT, UPDATE or DELETE operation.
Basic Trigger Example
Suppose we have a Students table and we want to automatically record an entry whenever a student is added.
CREATE TRIGGER trg_AfterInsertStudent
ON Students
AFTER INSERT
AS
BEGIN
PRINT 'New student inserted successfully.';
END;
Students
table.
Important Points
- A trigger executes automatically.
- Triggers are associated with database events.
- INSERT, UPDATE and DELETE are common trigger events.
- Triggers can help maintain data consistency.
- Triggers can be used for auditing and tracking changes.
- SQL Server supports different types of triggers for different requirements.
Summary
SQL Server Triggers are special database objects that automatically execute when specific database events occur. They are commonly used with INSERT, UPDATE and DELETE operations to maintain data integrity, track changes, and perform automatic database operations.