SQL TRIGGER

What is SQL TRIGGER?

A TRIGGER is a stored program that automatically runs when a specified INSERT, UPDATE, DELETE, or other database event occurs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TRIGGER:

SQL TRIGGER Full Explanation

A SQL TRIGGER is a named database object bound to a table or view that fires automatically when its triggering event happens. Events include data-change operations (INSERT, UPDATE, DELETE) as well as, in some dialects, DDL or logon actions. The trigger executes the statements defined in its body without explicit invocation from client code, enabling automated validation, auditing, cascading changes, and enforcement of complex business rules.Timing determines when the trigger fires: BEFORE triggers run prior to the row alteration, allowing modification of NEW/OLD records or aborting the operation; AFTER triggers execute only when the triggering statement completes successfully, ideal for logging or interaction with other tables. INSTEAD OF triggers (view-only in most systems) replace the triggering action entirely.Triggers can be row-level (fired once per affected row) or statement-level (fired once per SQL statement), and may include conditional logic via WHEN clauses. Because triggers run implicitly, they add hidden complexity: poorly written triggers can introduce performance bottlenecks, recursion, and debugging challenges. Always document trigger behavior, test for side effects, and keep logic idempotent.Standard SQL first specified triggers in SQL:1999, but each vendor adds proprietary syntax, limitations, and security rules (e.g., MySQL only allows one BEFORE and one AFTER trigger per table per event).

SQL TRIGGER Syntax

CREATE TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE }
    ON table_name
    [ FOR EACH { ROW | STATEMENT } ]
    [ WHEN (condition) ]
    EXECUTE PROCEDURE function_name(argument_list);

SQL TRIGGER Parameters

trigger_name STRING - Identifier for the trigger
BEFORE (AFTER) - INSTEAD OF KEYWORD|||Timing relative to the triggering event
INSERT (UPDATE) - DELETE KEYWORD|||Event that causes the trigger to fire
table_name IDENTIFIER - Table or view the trigger is attached to
FOR EACH ROW (STATEMENT KEYWORD) - Granularity at which the trigger fires
WHEN (condition) EXPRESSION - Optional Boolean expression that must evaluate to TRUE for the trigger to execute
function_name(argument_list) IDENTIFIER - Stored procedure or inline block that runs when triggered

Example Queries Using SQL TRIGGER

-- Auto-populate created_at before insert
CREATE OR REPLACE FUNCTION set_created_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.created_at := COALESCE(NEW.created_at, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_created_at
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE PROCEDURE set_created_at();

-- Audit email changes after update
CREATE OR REPLACE FUNCTION log_user_email()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO users_email_audit(user_id, old_email, new_email, changed_at)
  VALUES (OLD.id, OLD.email, NEW.email, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_email
AFTER UPDATE OF email ON users
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email)
EXECUTE PROCEDURE log_user_email();

Expected Output Using SQL TRIGGER

First trigger silently sets created_at for every new users row lacking a value
Second trigger writes an audit record whenever a user's email changes
No additional result sets are returned to the client

Use Cases with SQL TRIGGER

Enforce complex validations that constraints cannot cover
Maintain derived columns such as updated_at timestamps
Cascade changes to related tables (e.g., inventory adjustments)
Capture audit logs for compliance and debugging
Prevent invalid operations with BEFORE trigger checks
Implement soft deletes by rewriting DELETEs into status updates

Common Mistakes with SQL TRIGGER

Forgetting to RETURN NEW/OLD in row-level triggers, causing runtime errors
Creating recursive triggers that call themselves indefinitely
Hiding heavy logic in triggers, leading to unpredictable performance
Assuming trigger execution order when multiple triggers exist (vendor specific)
Not accounting for bulk operations, resulting in row-by-row overhead

First Introduced In

SQL:1999

Frequently Asked Questions

What events can fire a trigger?

INSERT, UPDATE, DELETE are universal. Some systems add TRUNCATE, DDL, LOGIN, or custom events.

Can I create multiple triggers for the same event?

Yes in PostgreSQL, SQL Server, and Oracle. MySQL allows only one BEFORE and one AFTER trigger per table per event.

How do I debug a trigger?

Use RAISE NOTICE/PRINT statements, write to log tables, or attach a debugger if the database supports it. Always test in a staging environment.

Are triggers part of the SQL standard?

Yes. Triggers were formally added in SQL:1999, but each vendor extends the syntax and feature set.