PL/SQL Stored Procedures & Triggers

Syllabus tag: Paper-II, Section-B, Topic IV.5 — PL/SQL Stored Procedures & Triggers

Definition

PL/SQL (Procedural Language/SQL) extends SQL with procedural constructs (variables, loops, conditionals, exception handling), allowing blocks of logic to run inside the database engine itself rather than in the client application.

PL/SQL Block Structure

DECLARE
    -- variable declarations
    v_salary NUMBER;
BEGIN
    -- executable statements
    SELECT salary INTO v_salary FROM Employee WHERE emp_id = 101;
EXCEPTION
    -- error handling
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No such employee');
END;

• DECLARE (optional) → BEGIN...END (mandatory, executable section) → EXCEPTION (optional, error handling).

Cursors

• A cursor is a pointer to the context area used to process a multi-row query result set row-by-row.
• Implicit cursor: automatically created by Oracle for single-row DML/SELECT statements.
• Explicit cursor: programmer-declared for a multi-row query, manipulated with DECLARE CURSOR, OPEN, FETCH, CLOSE.

DECLARE
    CURSOR emp_cur IS SELECT name, salary FROM Employee WHERE dept_id = 10;
    v_name Employee.name%TYPE;
    v_salary Employee.salary%TYPE;
BEGIN
    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO v_name, v_salary;
        EXIT WHEN emp_cur%NOTFOUND;
        -- process each row
    END LOOP;
    CLOSE emp_cur;
END;

Stored Procedures vs Functions

CREATE OR REPLACE PROCEDURE give_raise (p_emp_id IN NUMBER, p_pct IN NUMBER) AS
BEGIN
    UPDATE Employee SET salary = salary * (1 + p_pct/100) WHERE emp_id = p_emp_id;
    COMMIT;
END;

• Benefits of stored procedures: precompiled (faster execution), fewer network round-trips (logic runs server-side), reusable across applications, centralizes and secures business logic.

Triggers

• A trigger is a stored procedure automatically executed ("fired") in response to a specified database event — used to enforce business rules, maintain audit logs, or replicate data changes.
• Timing: BEFORE, AFTER, INSTEAD OF (used on views)
• Event: INSERT, UPDATE, DELETE (or combinations)
• Granularity: Row-level (FOR EACH ROW — fires once per affected row, can access :OLD/:NEW values) vs Statement-level (fires once per triggering statement, regardless of rows affected)

CREATE OR REPLACE TRIGGER audit_salary_update
AFTER UPDATE OF salary ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO Salary_Audit (emp_id, old_salary, new_salary, changed_on)
    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

MCQ Trap

• A trigger cannot be invoked directly by a user/application — it fires automatically on the specified event, unlike a stored procedure which must be explicitly called.
• :OLD and :NEW pseudo-records are only meaningful in row-level triggers, not statement-level ones.

CSS Exam Angle

• FPSC 2016 Q6(c) tested "different forms of triggers" directly — always cover timing (BEFORE/AFTER/INSTEAD OF) × granularity (row/statement) as the two independent axes that define a trigger's "form."
• Expect a "write a trigger for X business rule" style question (e.g., prevent salary decrease, log deletions) — practice the CREATE TRIGGER syntax by hand.

Likely Questions

1. What is a stored procedure? Discuss its benefits over embedding SQL directly in application code.
2. What is a trigger? Explain its different forms/types with an example.
3. Differentiate implicit and explicit cursors.
4. Write a trigger that logs every deletion from an Employee table into an audit table.
5. Differentiate a stored procedure from a function.

---

📘 Enrichment — Code You Should Be Able to Write by Hand

Example 1 — Procedure with IN/OUT Parameters and Exception Handling

CREATE OR REPLACE PROCEDURE transfer_funds (
    p_from   IN  NUMBER,
    p_to     IN  NUMBER,
    p_amount IN  NUMBER,
    p_status OUT VARCHAR2
) AS
    v_balance NUMBER;
    insufficient_funds EXCEPTION;              -- user-defined exception
BEGIN
    SELECT balance INTO v_balance FROM Account WHERE acc_no = p_from FOR UPDATE;
    IF v_balance < p_amount THEN
        RAISE insufficient_funds;
    END IF;
    UPDATE Account SET balance = balance - p_amount WHERE acc_no = p_from;
    UPDATE Account SET balance = balance + p_amount WHERE acc_no = p_to;
    COMMIT;
    p_status := 'SUCCESS';
EXCEPTION
    WHEN insufficient_funds THEN
        ROLLBACK; p_status := 'INSUFFICIENT BALANCE';
    WHEN NO_DATA_FOUND THEN
        ROLLBACK; p_status := 'ACCOUNT NOT FOUND';
    WHEN OTHERS THEN
        ROLLBACK; p_status := 'ERROR: ' || SQLERRM;
END;
/
-- Calling it
DECLARE v_msg VARCHAR2(100);
BEGIN
    transfer_funds(1001, 2002, 5000, v_msg);
    DBMS_OUTPUT.PUT_LINE(v_msg);
END;

• Shows everything examiners reward: parameters modes, FOR UPDATE row lock, user-defined exception, atomic COMMIT/ROLLBACK — and links PL/SQL to ACID atomicity.

Example 2 — Function Used Inside SQL

CREATE OR REPLACE FUNCTION annual_tax (p_salary NUMBER) RETURN NUMBER IS
BEGIN
    IF p_salary * 12 <= 600000 THEN
        RETURN 0;
    ELSE
        RETURN (p_salary * 12 - 600000) * 0.05;
    END IF;
END;
/
SELECT name, salary, annual_tax(salary) AS tax FROM Employee;

(Illustrative slab only — not the actual FBR rate.)

Example 3 — BEFORE Trigger Enforcing a Business Rule (prevent salary cut)

CREATE OR REPLACE TRIGGER no_salary_decrease
BEFORE UPDATE OF salary ON Employee
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased');
    END IF;
END;
/

Example 4 — Audit Trigger for Deletions (Likely Question 4)

CREATE TABLE Emp_Delete_Log (
    emp_id NUMBER, name VARCHAR2(50), deleted_by VARCHAR2(30), deleted_on DATE
);

CREATE OR REPLACE TRIGGER log_emp_delete
AFTER DELETE ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO Emp_Delete_Log VALUES (:OLD.emp_id, :OLD.name, USER, SYSDATE);
END;
/

• Uses :OLD because after a DELETE there is no :NEW row.

Example 5 — Statement-Level Trigger (restrict changes to office hours)

CREATE OR REPLACE TRIGGER office_hours_only
BEFORE INSERT OR UPDATE OR DELETE ON Employee
BEGIN
    IF TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '09' AND '17' THEN
        RAISE_APPLICATION_ERROR(-20002, 'Changes allowed only 9am-5pm');
    END IF;
END;
/

• No FOR EACH ROW → fires once per statement, even if the UPDATE touches 1,000 rows.

Example 6 — Cursor FOR Loop (the short form)

BEGIN
    FOR r IN (SELECT name, salary FROM Employee WHERE dept_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE(r.name || ' earns ' || r.salary);
    END LOOP;   -- OPEN, FETCH, CLOSE handled automatically
END;

Cursor Attributes

• Implicit cursor attributes are read through SQL%ROWCOUNT, e.g. after UPDATE … ; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');

Trigger Classification Matrix (the "forms" FPSC asked for)

• Other trigger kinds: DDL triggers (fire on CREATE/ALTER/DROP) and database/system event triggers (LOGON, LOGOFF, STARTUP, SERVERERROR).

Packages (bonus)

• A package groups related procedures, functions, variables and cursors: a specification (public interface) and a body (implementation) — gives encapsulation, better performance (loaded once), and overloading.

Trigger Pitfalls

• Mutating-table error (ORA-04091): a row-level trigger querying the same table it is modifying.
• Cascading/recursive triggers: trigger A updates a table that fires trigger B that fires A.
• Hidden logic makes debugging hard — keep triggers for integrity/auditing, not core business flow.

---

✍️ Model Answers — Likely Questions

Q1. What is a stored procedure? Discuss its benefits.

Definition: a named, precompiled PL/SQL (or T-SQL) block stored in the database and executed on the server by an explicit call, optionally with IN, OUT and IN OUT parameters.

Benefits:

1. Performance — parsed/compiled once, plan reused.
2. Reduced network traffic — one call replaces many SQL round-trips.
3. Security — grant EXECUTE on the procedure without granting direct table access; limits SQL injection.
4. Reusability & consistency — one implementation shared by web, mobile and desktop clients.
5. Maintainability — change business logic in one place.
6. Transaction control — whole business operation succeeds or rolls back together.

Example: the transfer_funds procedure above. Drawback: vendor-specific language reduces portability.

Q2. What is a trigger? Explain its different forms with an example.

Definition: a stored block that the DBMS executes automatically when a specified event (INSERT, UPDATE, DELETE, DDL, or system event) occurs on a table, view or schema. It follows the ECA model: Event → Condition → Action.

Forms = timing × granularity — present the 3 × 2 classification matrix above, with one example in each main cell.

Uses: enforcing complex integrity rules, auditing, derived-column maintenance, replication, security restrictions.

Example: log_emp_delete audit trigger. Caution: mutating-table and cascading-trigger issues.

Q3. Differentiate implicit and explicit cursors.

Include the emp_cur explicit cursor from the notes as the example.

Q4. Write a trigger that logs every deletion from Employee into an audit table.

Write Example 4 above: create the log table, then an AFTER DELETE … FOR EACH ROW trigger inserting :OLD values with USER and SYSDATE. Explain: AFTER (the delete is confirmed), row-level (one log row per deleted employee), :OLD (the deleted values; :NEW is empty on DELETE).

Q5. Differentiate a stored procedure from a function.

Use the comparison table in the notes, adding: a function must RETURN one value and can be called inside SELECT/WHERE (annual_tax example) provided it performs no DML; a procedure performs actions, may return several values through OUT parameters and is invoked with EXEC/CALL or from another block (transfer_funds example).

---

⚡ 60-Second Revision

• Block: DECLARE (optional) → BEGIN (mandatory) → EXCEPTION (optional) → END.
• Procedure = action, called explicitly; Function = returns one value, usable in SQL; Trigger = fires automatically, never called.
• Trigger form = BEFORE/AFTER/INSTEAD OF × ROW/STATEMENT.
• :OLD/:NEW only in row-level triggers; INSERT has no :OLD, DELETE has no :NEW.
• Cursor steps: DECLARE → OPEN → FETCH → CLOSE; exit on %NOTFOUND.

📜 Past-Paper Questions & MCQs (CSS 2016–2026)

2016 · Paper-II · Q6(c) · 6 marks

Q. A trigger is a statement that the system executes automatically as a side effect of a modification to the database. What are the different forms of triggers and how are they defined?

Ans.

• A trigger is stored code attached to a table that fires automatically on an INSERT, UPDATE or DELETE event.
• Forms (classification):
  • By timing: BEFORE triggers (validate or modify data before the change) and AFTER triggers (act after it, such as auditing). INSTEAD OF triggers (on views).
  • By event: INSERT, UPDATE and DELETE triggers.
  • By level: row-level (FOR EACH ROW, fires once per affected row) and statement-level (fires once per statement).
• Definition (syntax):

CREATE TRIGGER audit_salary
AFTER UPDATE OF salary ON Employee
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)
BEGIN
    INSERT INTO SalaryLog(empId, oldSal, newSal, changedOn)
    VALUES (:OLD.empId, :OLD.salary, :NEW.salary, SYSDATE);
END;

• Uses: enforcing business rules, auditing, maintaining derived data and replication. Caution: triggers can hide logic and slow updates.