Working with Cursors and Triggers in Oracle Live SQL

#sql #database #tutorial

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)

DECLARE
emp_name Employee.Emp_Name%TYPE;

CURSOR emp_cursor IS
SELECT Emp_Name FROM Employee WHERE Salary > 50000;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || 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:**

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

👉 Example (Oracle PL/SQL):

CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Course VARCHAR(30)
);

CREATE TABLE Student_Audit (
Audit_ID INT PRIMARY KEY,
Student_ID INT,
Action VARCHAR(20),
Log_Time TIMESTAMP
);

CREATE SEQUENCE STUDENT_AUDIT_SEQ
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT
ON STUDENTS
FOR EACH ROW
BEGIN
INSERT INTO STUDENT_AUDIT (AUDIT_ID, STUDENT_ID, ACTION, LOG_TIME)
VALUES (STUDENT_AUDIT_SEQ.NEXTVAL, :NEW.STUDENT_ID, 'INSERT', SYSTIMESTAMP);
END;
/

INSERT INTO STUDENTS VALUES (1, 'Sowmya', 'Biology');

SELECT * FROM STUDENT_AUDIT;