PL/SQL Triggers: Row/Statement, BEFORE/AFTER, and Real-Time Logic

TAKEAWAYS:

• Triggers are event-driven PL/SQL blocks that execute automatically when a DML (INSERT/UPDATE/DELETE) or DDL (CREATE/ALTER) event occurs.
• Row-level triggers fire once per row affected; statement-level triggers fire once per statement, enabling batch processing optimizations.
• BEFORE triggers validate data (e.g., check salary before updating), while AFTER triggers enforce post-action logic (e.g., log changes).
• Real-world use: eSewa uses triggers to auto-debit fees when transactions exceed limits; Daraz triggers update inventory after order confirmations.
• Triggers avoid application code redundancy but can complicate debugging if overused.
• Always test triggers with INSERT/UPDATE/DELETE statements to verify timing (BEFORE/AFTER).

1. What Is a Trigger?

A trigger is a stored PL/SQL procedure that executes automatically in response to a database event (e.g., inserting a record). It acts like a guardian for data integrity, enforcing rules without manual intervention.

Key Characteristics

Why Use Triggers?

2. Trigger Syntax and Structure

A trigger has three mandatory components:

1. Trigger Name (e.g., chk_salary)
2. Event (e.g., BEFORE UPDATE ON employees)
3. Trigger Body (PL/SQL code to execute)

Basic Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
[FOR EACH ROW | WHEN (condition)]
DECLARE
    -- Variables (optional)
BEGIN
    -- PL/SQL logic
EXCEPTION
    -- Error handling (optional)
END;

Example: Row-Level Trigger

Scenario: Auto-set a last_updated timestamp when an employee record is modified.

CREATE OR REPLACE TRIGGER set_employee_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.last_updated := SYSDATE;
END;

• :NEW refers to the new row (after update).
• :OLD refers to the old row (before update, only for UPDATE/DELETE).

3. Row-Level vs. Statement-Level Triggers

Example: Statement-Level Trigger

Scenario: Log all deletions to an audit_log table.

CREATE OR REPLACE TRIGGER log_deletions
AFTER DELETE ON products
BEGIN
    INSERT INTO audit_log (action, table_name, record_id)
    VALUES ('DELETE', 'products', :OLD.product_id);
END;

• Uses INSERTING, UPDATING, DELETING tables to check if the trigger fired.

4. BEFORE vs. AFTER Triggers

Worked Example: BEFORE Trigger for Salary Validation

Scenario: Prevent negative salaries in the employees table.

CREATE OR REPLACE TRIGGER chk_salary_negative
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative!');
    END IF;
END;

Test Case:

UPDATE employees SET salary = -5000 WHERE emp_id = 101;

Output:

ORA-20001: Salary cannot be negative!

Worked Example: AFTER Trigger for Inventory Update

Scenario: Auto-deduct stock when an order is placed (simulating Daraz’s inventory system).

CREATE OR REPLACE TRIGGER update_inventory_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock_quantity = stock_quantity - :NEW.quantity
    WHERE product_id = :NEW.product_id;
END;

Test Case:

INSERT INTO orders (order_id, product_id, quantity)
VALUES (1001, 501, 3);

Result: Stock for product 501 decreases by 3.

5. Trigger Events and Timing

Triggers can fire on:

• DML Events: INSERT, UPDATE, DELETE
• DDL Events: CREATE, ALTER, DROP (requires SYSDATE or DUMP functions)
• Statement Events: INSERTING, UPDATING, DELETING (for statement-level triggers)

sequenceDiagram
    participant User
    participant DB
    participant BEFORE_Trigger
    participant AFTER_Trigger

    User->>DB: INSERT INTO orders (product_id, quantity)
    DB->>BEFORE_Trigger: Execute (e.g., validate stock)
    BEFORE_Trigger-->>DB: Allow/Reject
    DB->>AFTER_Trigger: Execute (e.g., update inventory)
    AFTER_Trigger-->>DB: Confirm
    DB-->>User: Success/Failure

Trigger Execution Flow

sequenceDiagram
    participant User
    participant DB
    participant Trigger

    User->>DB: UPDATE employees SET salary = 5000 WHERE emp_id = 101
    DB->>Trigger: Execute BEFORE UPDATE trigger (chk_salary_negative)
    alt Salary valid
        Trigger->>DB: Allow update
    else Salary invalid
        Trigger->>DB: RAISE_ERROR
    end
    DB->>Trigger: Execute AFTER UPDATE trigger (set_employee_timestamp)
    DB->>User: Confirm update

6. Real-World Applications

## In the real world

1. eSewa Fraud Prevention

   • Idea: AFTER INSERT trigger on transactions table to check if the transaction amount exceeds the user’s daily limit.
   • How: If :NEW.amount > :OLD.daily_limit, the trigger rolls back the transaction and sends an alert.
   • Example: A user tries to transfer ₹50,000 (daily limit: ₹20,000). The trigger blocks it and notifies support.

2. Daraz Order Validation

   • Idea: BEFORE INSERT trigger on orders to ensure stock availability.
   • How: Before inserting an order, the trigger checks stock_quantity in the products table. If insufficient, it rejects the order.
   • Example: A customer orders 10 units of a product with only 5 in stock. The trigger prevents the order and suggests alternatives.

3. NEPSE Stock Market

   • Idea: AFTER UPDATE trigger on trades to auto-update the portfolio table for investors.
   • How: When a stock price changes, the trigger recalculates the investor’s holdings and updates their portfolio value.
   • Example: If a stock’s price drops, the trigger adjusts the investor’s net worth in real time.

7. Trigger Limitations and Best Practices

Advantages

• Automation: No need for application code to enforce rules.
• Data Integrity: Ensures constraints are met at the database level.
• Audit Trails: Logs changes automatically.

Disadvantages

• Debugging Complexity: Triggers can create "spaghetti code" if overused.
• Performance Overhead: Row-level triggers slow down large DML operations.
• Version Control: Harder to manage than application logic.

Best Practices

1. Keep Triggers Simple: Avoid complex logic; move it to stored procedures if needed.
2. Use Comments: Document the purpose of each trigger.
3. Test Thoroughly: Test with INSERT, UPDATE, and DELETE statements.
4. Avoid Cascading Triggers: Multiple triggers on the same event can cause unexpected behavior.
5. Use PRAGMA AUTONOMOUS_TRANSACTION: For triggers that need to commit independently (e.g., sending emails).

8. Debugging Triggers

Common Issues and Fixes

Debugging Steps

1. Check Trigger Syntax:

   SHOW TRIGGERS;
   

2. Test Manually:

   INSERT INTO employees (emp_id, name, salary) VALUES (102, 'Alice', 45000);
   

3. Enable SQL Trace (for advanced debugging):

   ALTER SYSTEM SET sql_trace = true;
   

9. Exam Tip

• Focus on Syntax: Know the exact structure of CREATE TRIGGER for row/statement and BEFORE/AFTER.
• Compare Row vs. Statement: Understand when to use each (e.g., row for validation, statement for logging).
• Practice Timing: Write triggers that fire before (validation) and after (logging) events.
• Real-World Link: Relate triggers to eSewa fraud checks, Daraz stock updates, or NEPSE portfolio adjustments.
• Common Pitfalls: Avoid infinite recursion; test with edge cases (e.g., NULL values).
• Short Answer Tip: For "differentiate between anonymous and named blocks," mention that triggers are named blocks tied to events, while anonymous blocks are one-time scripts.

In the real world

• eSewa: Uses AFTER INSERT triggers on transactions to auto-debit fees when amounts exceed user limits (e.g., ₹50,000 > ₹20,000 daily cap).
• Daraz: AFTER INSERT triggers on orders deduct stock from products table (e.g., UPDATE products SET stock = stock - :NEW.quantity).
• Nepal Rastra Bank (NRB): BEFORE UPDATE triggers validate loan applications (e.g., IF :NEW.loan_amount > :OLD.max_limit THEN RAISE_ERROR).