SQL Triggers Fundamentals

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