SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEAdvanced

SQL Lesson 25: SQL Triggers (Automated Events)

Introduction & Core Concept

SQL Server triggers are specialized, event-driven database objects that execute automatically in response to specific statements. Unlike standard stored procedures, you cannot manually execute a trigger—they fire automatically when their associated events occur.

SQL Server provides three primary types of triggers:

1. DML (Data Manipulation Language) Triggers: Fire in response to INSERT, UPDATE, or DELETE events on a table. DML triggers can be classified into AFTER triggers (executed after the action completes) and INSTEAD OF triggers (executed *instead of* the triggering action).

2. DDL (Data Definition Language) Triggers: Fire in response to schema mutations like CREATE_TABLE, ALTER_TABLE, or DROP_TABLE.

3. Logon Triggers: Fire in response to database session establishment events (LOGON).

Inside DML triggers, SQL Server provides two memory-resident virtual tables for checking changes:

  • inserted: Contains new or updated row data post-mutation.
  • deleted: Contains historical, pre-mutation, or deleted row data.
    • Why & Where We Use It
  • Audit Logging: Recording exact before-and-after states of database modifications for financial or legal auditing.
  • Enforcing Relational Safety: Running validations that are too complex to represent with standard table constraints.
  • Preventing Schema Changes: Using DDL triggers to block junior developers or unauthorized users from dropping crucial database structures.
  • Soft Deletion: Intercepting delete operations using INSTEAD OF triggers to flag rows as inactive instead of permanently erasing them.
    • Real-World Example

    ApexBank needs to keep track of any structural deletions or alterations on their database schemas to prevent structural outages. They also employ DML audit triggers to track user transactions and log security events dynamically.

    Best Practices: What to Do & What NOT to Do
  • What to Do: Always write triggers with multi-row logic in mind. SQL Server triggers fire once per statement, not once per row! If a user inserts 1,000 rows in a single query, your trigger fires only once, and the inserted table will contain all 1,000 rows.
  • What to Do: Use EVENTDATA() inside DDL triggers to parse and log the exact XML detail of the schema change.
  • What NOT to Do: Do not execute heavy transactions, long-running math operations, or external network requests inside triggers. Relational database locks are held active until the trigger code fully completes; slow triggers will freeze active systems!
  • What NOT to Do: Avoid enabling recursive or nested triggers unless absolutely necessary, to prevent infinite evaluation loops that exhaust system stack limits.
    • Syntax & Pro Tips

    ##### 1. Creating DML Triggers (Insert & Delete Events)

    CREATE TRIGGER trg_AuditProducts
ON products
AFTER INSERT, DELETE
AS
BEGIN
  -- Handle Insert Audit
  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
  BEGIN
    INSERT INTO audit_log (prod_id, action_taken)
    SELECT id, 'INSERT' FROM inserted;
  END
  
  -- Handle Delete Audit
  IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
  BEGIN
    INSERT INTO audit_log (prod_id, action_taken)
    SELECT id, 'DELETE' FROM deleted;
  END
END;

    ##### 2. Creating an INSTEAD OF Trigger

    CREATE TRIGGER trg_SoftDeleteProduct
ON products
INSTEAD OF DELETE
AS
BEGIN
  -- Bypass physical deletion: mark status as Discontinued
  UPDATE products 
  SET stock = 0, category = 'Discontinued'
  WHERE id IN (SELECT id FROM deleted);
END;

    ##### 3. Creating DDL Triggers

    CREATE TRIGGER trg_PreventTableDrop
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
  -- Capture change details and rollback transaction
  PRINT 'Database modifications are prohibited on this schema!';
  ROLLBACK;
END;

    ##### 4. Disabling & Enabling Triggers

    -- Disable a trigger temporarily
DISABLE TRIGGER trg_AuditProducts ON products;

-- Enable it back
ENABLE TRIGGER trg_AuditProducts ON products;

    ##### 5. Viewing Trigger Definitions

    -- Query using the system stored procedure
EXEC sp_helptext 'trg_AuditProducts';

-- Or fetch using sys.sql_modules
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('trg_AuditProducts');

    ##### 6. Listing All Triggers in SQL Server

    SELECT name, parent_class_desc, is_instead_of_trigger, is_disabled 
FROM sys.triggers;

    ##### 7. Removing Triggers

    -- Drop DML trigger
DROP TRIGGER trg_SoftDeleteProduct;

-- Drop DDL trigger (must specify scope)
DROP TRIGGER trg_PreventTableDrop ON DATABASE;
    Interactive Sandboxed Terminal (Preloaded DB Schema: SHOPMART)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1