Stored Procedures, Triggers & Advanced SQL: Code, Events & Optimization

Core Concepts: Stored Procedures, Triggers, and Advanced SQL

1. Stored Procedures: Modular Code in the Database

Stored procedures are precompiled SQL code blocks stored in the database that execute when called. They improve performance, security, and reusability.

How They Work

• Definition: A named collection of SQL statements and optional control-of-flow statements (IF, WHILE, etc.).
• Execution: Called via CALL or embedded in application code.
• Parameters: Can accept input/output parameters (IN, OUT, INOUT).

Syntax (MySQL/PostgreSQL)

CREATE PROCEDURE procedure_name([parameter_mode parameter_name data_type])
BEGIN
    -- SQL statements
    DECLARE variable_name data_type;
    SET variable_name = value;
    IF condition THEN
        -- logic
    END IF;
END;

Example: A procedure to calculate loan interest for a bank:

CREATE PROCEDURE CalculateLoanInterest(
    IN principal DECIMAL(10,2),
    IN rate DECIMAL(5,2),
    IN years INT,
    OUT total DECIMAL(10,2)
)
BEGIN
    SET total = principal * (1 + (rate/100)*years);
END;

Call:

CALL CalculateLoanInterest(100000, 8.5, 5, @result);
SELECT @result AS total_interest;

Advantages

✔ Performance: Precompiled, reducing network traffic. ✔ Security: Limits direct table access via application code. ✔ Reusability: Single call for complex operations.

Disadvantages

✖ Portability: Syntax varies across DBMS (MySQL, Oracle, SQL Server). ✖ Debugging: Harder to trace than application-level code.

2. Triggers: Automatic Event-Driven Actions

Triggers are special stored procedures that execute automatically in response to database events (INSERT, UPDATE, DELETE).

Types of Triggers

Syntax (MySQL)

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL logic
END;

Example: Auto-update last_updated on Orders table:

DELIMITER //
CREATE TRIGGER update_order_timestamp
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
    UPDATE Orders SET last_updated = NOW() WHERE order_id = NEW.order_id;
END//
DELIMITER ;

Real-World Use Case: E-Sewa Transaction Logging

When a user pays a bill via E-Sewa, a BEFORE INSERT trigger logs the transaction in an AuditLog table:

CREATE TRIGGER log_esewa_transaction
BEFORE INSERT ON Transactions
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (transaction_id, user_id, amount, timestamp)
    VALUES (NEW.transaction_id, NEW.user_id, NEW.amount, NOW());
END;

3. Advanced SQL Features

A. Dynamic SQL

Allows runtime SQL generation (e.g., building queries based on user input). Example: Dynamic table name in a procedure:

SET @table_name = 'Employees';
SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE salary > 50000');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

B. Cursors

Used for row-by-row processing in loops. Example: Update all overdue books in a library:

DECLARE done INT DEFAULT FALSE;
DECLARE book_id INT;
DECLARE cur CURSOR FOR SELECT book_id FROM BookIssue WHERE due_date < CURDATE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO book_id;
    IF done THEN
        LEAVE read_loop;
    END IF;
    UPDATE Books SET status = 'Overdue' WHERE book_id = book_id;
END LOOP;
CLOSE cur;

C. Transactions and Concurrency Control

Transactions ensure ACID (Atomicity, Consistency, Isolation, Durability). Example: Bank transfer (with BEGIN, COMMIT, ROLLBACK):

BEGIN;
UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE account_id = 2;
-- If any error occurs, ROLLBACK;
COMMIT;

In the Real World

1. Khalti (Digital Payments)

   • Stored Procedures: Used for fraud detection (e.g., CheckTransactionFraud() runs complex checks before approving a payment).
   • Triggers: AFTER INSERT on Transactions logs all payments in an immutable AuditLog for compliance.

2. Pathao (Ride-Hailing)

   • Dynamic SQL: Generates real-time ride quotes based on distance, time, and demand:

     SET @sql = CONCAT('SELECT * FROM Rates WHERE pickup_zone = ''', @pickup, ''' AND dropoff_zone = ''', @dropoff, '''');
     PREPARE stmt FROM @sql;
     EXECUTE stmt;
     

   • Triggers: BEFORE UPDATE on RiderStatus sends push notifications when a rider accepts a ride.

3. Nepal Stock Exchange (NEPSE)

   • Stored Procedures: CalculateDividend() computes shareholder payouts monthly.
   • Transactions: Ensures no double-selling of shares (atomic BUY/SELL operations).

Comparison: Stored Procedures vs. Triggers

Exam Tip

1. Syntax Matters: Memorize CREATE PROCEDURE, DELIMITER, and trigger syntax (MySQL/Oracle/SQL Server variations).
2. Real-World Scenarios: Expect questions on banking (transactions), e-commerce (order processing), or government systems (audit logs).
3. Error Handling: Show BEGIN/COMMIT/ROLLBACK for transactions and DECLARE CONTINUE HANDLER for cursors.
4. Diagrams: Draw a layered model of how stored procedures/triggers interact with the DBMS and application layer.

sequenceDiagram
    participant App as Application
    participant DB as Database
    participant SP as Stored Procedure
    participant Trigger as Trigger

    App->>DB: CALL CalculateLoanInterest(100000, 8.5, 5)
    DB->>SP: Executes precompiled logic
    SP-->>DB: Returns total_interest
    DB-->>App: Result

    loop On INSERT into Orders
        Trigger->>DB: Logs to AuditLog
    end

Caption: Stored procedures vs. triggers in action.

Caption: Relationship between stored procedures and triggers.