🚀 Working with Cursors and Triggers in Oracle Live SQL

In this post, we’ll learn how to use Cursors and Triggers in Oracle SQL with simple examples.
Let’s dive in! ⚡

📌 1. Cursor with Condition

A cursor in Oracle is used when you want to process query results row by row.
👉 Example: Display employee names whose salary > 50,000 from the Employee table.

✅ Steps:

1. Declare a cursor with the condition.
2. Open the cursor.
3. Fetch each row into a variable.
4. Process inside a loop.
5. Close the cursor.

💻 Example (Oracle PL/SQL):

SET SERVEROUTPUT ON;

DECLARE
    CURSOR emp_cursor IS
        SELECT emp_name FROM Employee WHERE salary > 50000;
    v_emp_name Employee.emp_name%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
    END LOOP;
    CLOSE emp_cursor;
END;
/

📌 2. AFTER INSERT Trigger

A trigger in Oracle is a stored PL/SQL block that automatically executes when a specific event occurs on a table.
👉 Example: Whenever a new student is inserted into the Students table, add a log entry in Student_Audit table.

✅ Steps:

1. Create an audit table.
2. Write an AFTER INSERT trigger.
3. Insert log details inside the trigger.

💻 Example (Oracle PL/SQL):

CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    student_name VARCHAR2(100)
);

CREATE TABLE Student_Audit (
    audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id NUMBER,
    student_name VARCHAR2(100),
    action_date DATE DEFAULT SYSDATE
);

Create the AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit (student_id, student_name)
    VALUES (:NEW.student_id, :NEW.student_name);
END;
/

INSERT INTO Students (student_id, student_name) 
VALUES (1, 'Alice');

INSERT INTO Students (student_id, student_name) 
VALUES (2, 'Bob');

Check the Audit Table

SELECT * FROM Student_Audit;

🎯 Conclusion

1. Cursors are useful when you need row-by-row processing.
2. Triggers automate tasks like auditing changes.