cursor+trigger

Cursor + Trigger — SQL Practice
In this post, we’ll explore two powerful SQL concepts — Cursors and Triggers — with complete examples that you can run directly in Oracle LiveSQL.

CURSOR — Process Cursor with Condition
Problem Statement:

Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.
CREATE TABLE Employee (
EmpID NUMBER PRIMARY KEY,
EmpName VARCHAR2(100),
Salary NUMBER(10,2)
);
INSERT INTO Employee VALUES (1, 'Arjun', 45000);
INSERT INTO Employee VALUES (2, 'Sneha', 52000);
INSERT INTO Employee VALUES (3, 'Kiran', 60000);
INSERT INTO Employee VALUES (4, 'Meena', 48000);
COMMIT;
SET SERVEROUTPUT ON;

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

*TRIGGER *— AFTER INSERT Trigger (Student Table)
Problem Statement:

Whenever a new student is added to the Students table, automatically insert a log entry into the Student_Audit table to keep track of the registration.
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(100),
Course VARCHAR2(100)
);
CREATE TABLE Student_Audit (
AuditID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID NUMBER,
StudentName VARCHAR2(100),
ActionTaken VARCHAR2(100),
ActionDate DATE
);
CREATE OR REPLACE TRIGGER trg_AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName, ActionTaken, ActionDate)
VALUES (:NEW.StudentID, :NEW.StudentName, 'New Student Registered', SYSDATE);
END;
INSERT INTO Students (StudentID, StudentName, Course)
VALUES (1, 'hareesh', 'Computer Science');
COMMIT;
SELECT * FROM Student_Audit;