Stored Procedures and Triggers in Oracle: A Complete Guide

Introduction

Oracle Database is a powerful relational database management system (RDBMS) widely used in enterprise applications. Two of its most essential features for automating business logic and maintaining data integrity are stored procedures and triggers.

This guide covers:
✔ What are stored procedures and triggers?
✔ Key differences between them
✔ How to create, execute, and manage them
✔ Best practices and real-world use cases

1. What Are Stored Procedures?

A stored procedure is a precompiled collection of SQL and PL/SQL statements stored in the database. It performs a specific task when called, improving performance, security, and code reusability.

Advantages of Stored Procedures

✅ Performance – Precompiled and cached for faster execution.
✅ Security – Restrict direct table access; grant execution rights instead.
✅ Reusability – Call the same logic from multiple applications.
✅ Maintainability – Centralize business logic in the database.

Basic Syntax

CREATE OR REPLACE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype)  
IS  
  -- Variable declarations  
BEGIN  
  -- Business logic  
  UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;  
  COMMIT;  
EXCEPTION  
  WHEN OTHERS THEN  
    ROLLBACK;  
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);  
END;  
/

How to Execute a Stored Procedure

-- Method 1: Using EXEC  
EXEC procedure_name(param1, param2);  

-- Method 2: Using CALL  
CALL procedure_name(param1, param2);  

-- Method 3: From PL/SQL block  
BEGIN  
  procedure_name(param1, param2);  
END;  
/

2. What Are Triggers?

A trigger is a special kind of stored procedure that automatically executes in response to a specific database event (e.g., INSERT, UPDATE, DELETE).

Types of Triggers

Trigger Type	Description
BEFORE	Fires before the triggering event
AFTER	Fires after the triggering event
INSTEAD OF	Used on views to modify underlying tables
ROW-Level	Executes for each affected row
STATEMENT-Level	Executes once per SQL statement

Basic Syntax

CREATE OR REPLACE TRIGGER trigger_name  
BEFORE INSERT OR UPDATE ON employees  
FOR EACH ROW  -- Row-level trigger  
BEGIN  
  -- Business logic  
  IF :NEW.salary < 0 THEN  
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative!');  
  END IF;  
END;  
/

Common Use Cases for Triggers

✔ Audit logging (track who modified data)
✔ Enforcing business rules (e.g., salary validation)
✔ Auto-generating values (like invoice numbers)
✔ Maintaining derived data (e.g., updating a summary table)

3. Key Differences Between Stored Procedures and Triggers

Feature	Stored Procedure	Trigger
Execution	Manually called	Automatic (on event)
Parameters	Supports input/output	No direct parameters (:NEW, :OLD)
Transaction Control	Can commit/rollback	Should not commit/rollback
Usage	Business logic, reports	Data validation, auditing

4. Best Practices

For Stored Procedures

✔ Use meaningful names (e.g., calculate_bonus, not proc1)
✔ Avoid hardcoding values (use parameters)
✔ Include error handling (EXCEPTION block)
✔ Optimize SQL inside (avoid full table scans)

For Triggers

✔ Keep them simple (complex logic belongs in procedures)
✔ Avoid recursive triggers (can cause infinite loops)
✔ Document triggers (since they fire automatically)
✔ Test thoroughly (they can silently break workflows)

5. Real-World Examples

Example 1: Stored Procedure for Employee Bonus Calculation

CREATE OR REPLACE PROCEDURE calculate_bonus (dept_id NUMBER)  
IS  
  bonus_percent NUMBER := 0.15;  
BEGIN  
  UPDATE employees  
  SET salary = salary + (salary * bonus_percent)  
  WHERE department_id = dept_id;  
  COMMIT;  
  DBMS_OUTPUT.PUT_LINE('Bonus applied to department ' || dept_id);  
EXCEPTION  
  WHEN OTHERS THEN  
    ROLLBACK;  
    DBMS_OUTPUT.PUT_LINE('Error in calculate_bonus: ' || SQLERRM);  
END;  
/

Example 2: Trigger for Audit Logging

CREATE OR REPLACE TRIGGER log_salary_changes  
AFTER UPDATE OF salary ON employees  
FOR EACH ROW  
BEGIN  
  INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)  
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);  
END;  
/

6. When to Use Stored Procedures vs. Triggers

Scenario	Recommended Approach
Complex business logic	Stored Procedure
Data validation before saving	BEFORE Trigger
Audit logging	AFTER Trigger
Batch processing	Stored Procedure
Derived column updates	INSTEAD OF Trigger (for views)

Conclusion

Stored procedures and triggers are powerful tools in Oracle for automating workflows, enforcing rules, and improving efficiency.

🔹 Use stored procedures for reusable business logic.
🔹 Use triggers for automatic actions on data changes.
🔹 Follow best practices to avoid performance issues.

By mastering both, you can build more robust, efficient, and secure database applications.

💡 Next Steps:

• Experiment with creating your own procedures and triggers.

• Explore Oracle’s PL/SQL documentation for advanced features.

• Consider database design patterns where these fit best.

Have questions? Drop them in the comments! 👇

#Oracle #Database #SQL #PLSQL