What Are Database Triggers?

A trigger is a specialized database object that automatically executes in response to certain events on a specific table or view. Unlike regular stored procedures, triggers cannot be invoked manually—they fire automatically when INSERT, UPDATE, or DELETE operations occur on their associated table.

Triggers serve several critical purposes in database management:

• Enforcing complex business rules that cannot be handled by constraints alone
• Maintaining audit trails and logging changes
• Ensuring data integrity across related tables
• Automating cascading operations

How Triggers Differ from Stored Procedures

The fundamental distinction lies in execution model. Stored procedures require explicit invocation through applications, scripts, or other procedures. Triggers, however, respond automatically when predefined data modification events take place.

Aspect	Stored Procedure	Trigger
Invocation	Manual call required	Event-driven automatic execution
Parameters	Can accept input/output parameters	Cannot accept parameters
Scope	Independent database object	Tied to specific table and event

SQL Server Trigger Architecture

SQL Server maintains two temporary in-memory tables for each trigger:

• Inserted table: Contanis newly added or modified rows
• Deleted table: Contains removed or original values before update

These tables exist only during trigger execution and are automatically cleaned up afterward. Their structure mirrors the triggering table.

Key Considerations

1. One trigger per table-event combination: SQL Server permits only one trigger per table for each combination of timing (BEFORE/AFTER) and event type (INSERT/UPDATE/DELETE). Attempting to create a duplicate results in an error.

2. Cleanup: Remove triggers that are no longer needed to prevent unintended behavior and reduce maintenance overhead.

3. Transaction handling: Triggers execute within the same transaction as the triggering statement. A rollback in the trigger affects the entire operation.

Trigger Classifications

DDL Triggers

Database-level triggers that respond to schema modifications like CREATE, ALTER, or DROP statements. Use cases include:

• Preventing unauthorized schema changes
• Auditing structural modifications
• Enforcing naming conventions

DML Triggers

Table-level triggers responding to data manipulation events. They can:

• Reference other tables for complex validations
• Perform cascading updates across relationships
• Maintain synchronization between related data

Logon Triggers

Fire during SQL Server authentication, after validation but before session establishment.

MySQL DML Trigger Implementation

Basic Syntax

DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- trigger logic here
END$$
DELIMITER ;

Setup Tables

CREATE TABLE user_balance (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    holder_name VARCHAR(50),
    balance DECIMAL(10,2)
);

INSERT INTO user_balance VALUES 
    (NULL, 'Alice', 5000.00),
    (NULL, 'Bob', 3000.00);

CREATE TABLE audit_trail (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    action_type VARCHAR(20),
    timestamp DATETIME,
    affected_account INT,
    details TEXT
);

INSERT Trigger Example

DELIMITER $$
CREATE TRIGGER track_new_accounts
AFTER INSERT ON user_balance
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail VALUES (
        NULL,
        'INSERT',
        NOW(),
        NEW.account_id,
        CONCAT('New account created: ', NEW.holder_name, ', initial balance: ', NEW.balance)
    );
END$$
DELIMITER ;

INSERT INTO user_balance VALUES (NULL, 'Charlie', 4500.00);

UPDATE Trigger Example

DELIMITER $$
CREATE TRIGGER track_balance_changes
AFTER UPDATE ON user_balance
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail VALUES (
        NULL,
        'UPDATE',
        NOW(),
        NEW.account_id,
        CONCAT(
            'Before: {id=', OLD.account_id, ', holder=', OLD.holder_name, ', balance=', OLD.balance, '}. ',
            'After: {id=', NEW.account_id, ', holder=', NEW.holder_name, ', balance=', NEW.balance, '}'
        )
    );
END$$
DELIMITER ;

UPDATE user_balance SET balance = 6000.00 WHERE account_id = 1;

DELETE Trigger Example

DELIMITER $$
CREATE TRIGGER archive_removed_accounts
AFTER DELETE ON user_balance
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail VALUES (
        NULL,
        'DELETE',
        NOW(),
        OLD.account_id,
        CONCAT('Removed: {id=', OLD.account_id, ', holder=', OLD.holder_name, ', balance=', OLD.balance, '}')
    );
END$$
DELIMITER ;

DELETE FROM user_balance WHERE account_id = 2;

Using OLD and NEW Keywords

In MySQL triggers, use NEW to reference newly inserted or modified rows, and OLD to reference rows being updated or deleted. These pseudo-records allow you to access both pre-change and post-change values for comparison and logging purposes.