This content originally appeared on DEV Community and was authored by Lohita Blue
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;
Conclusion
1.Cursors are useful when you need row-by-row processing.
2.Triggers automate tasks like auditing changes.
This content originally appeared on DEV Community and was authored by Lohita Blue