SQL Triggers Fundamentals

Study Notes

Triggers Overview

Triggers are event-driven procedures managed by the Database Management System (DBMS).
They automatically execute following data modifications to the associated table.
Main purpose is to maintain data integrity systematically.

Trigger Naming and Types

Maximum size for a trigger name: 128 characters.
Two primary trigger forms:
- DML (Data Manipulation Language) Triggers
- DDL (Data Definition Language) Triggers

DML Triggers

Invoked by DML events: INSERT, UPDATE, or DELETE.
Can query other tables and involve complex Transact-SQL statements.
Benefits of DML triggers:
- Prevent incorrect data modifications.
- Enforce complex restrictions beyond CHECK constraints.
- Evaluate table states before and after modifications.

Example of DML Trigger

Prevent modification in a specific table:

CREATE TRIGGER abort ON student 
AFTER INSERT, UPDATE, DELETE 
AS 
PRINT 'this task is not allowed' 
ROLLBACK

DDL Triggers

Triggered by DDL statements like CREATE, ALTER, DROP.
Used for administrative tasks, including auditing and regulating operations.
Ideal for:
- Preventing schema changes.
- Responding to schema changes.
- Recording schema modifications.

Example of DDL Trigger

Prevent modifications to any table in the database:

CREATE TRIGGER safety ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
ROLLBACK

Classification of Triggers

Triggers can generally be classified into:
- After Triggers (For Triggers)
- Instead Of Triggers

After Triggers

Execute after an insert, update, or delete operation.
Not applicable for views.
Can be further categorized into:
- AFTER INSERT
- AFTER UPDATE
- AFTER DELETE

Description

Learn about the basics of triggers in SQL, how they are stored and managed by the DBMS, and their role in maintaining referential integrity of data. Understand how triggers are automatically fired by the DBMS as a result of data modification.