SQL Triggers Fundamentals

There are three action query types that you use in SQL which are ______, UPDATE and DELETE.

Triggers are classified into two main types: ______ Triggers (For Triggers) and Instead Of Triggers.

AFTER TRIGGERS can be classified further into three types as: AFTER ______ Trigger, AFTER UPDATE Trigger, and AFTER DELETE Trigger.

These triggers run after an ______, update or delete on a table.

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

DDL trigger can be used to prevent any table in a database from being ______ or dropped.

______ Triggers are not supported for views.

There are three types of triggers and hybrids that come from mixing and matching the events and ______ that fire them.

Let’s create ______ triggers.

First of all, let’s create a table and ______ some sample data.

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

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.