Cursor and Trigger

#sql #database #oracle #tutorial

🔹 Cursor

A cursor is like a pointer that goes through each row of a query result one at a time.

We’ll create a cursor to:

Display employee names whose salary is greater than 50,000.

🔹 Trigger

A trigger is like an automatic reaction —
It executes automatically when an event (INSERT/UPDATE/DELETE) happens on a table.

We’ll create an AFTER INSERT trigger to:

Automatically insert a record into a Student_Audit table whenever a new student is added to the Students table.
✅ Step 1: Create the Employee Table
-- Drop if already exists
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EMPLOYEE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE EMPLOYEE (
EmpID NUMBER PRIMARY KEY,
EmpName VARCHAR2(50),
Salary NUMBER
);

INSERT INTO EMPLOYEE VALUES (1, 'Kiran', 45000);
INSERT INTO EMPLOYEE VALUES (2, 'Asha', 52000);
INSERT INTO EMPLOYEE VALUES (3, 'Sneha', 60000);
INSERT INTO EMPLOYEE VALUES (4, 'Rahul', 40000);
INSERT INTO EMPLOYEE VALUES (5, 'Vikram', 80000);

COMMIT;

✅ Step 2: Create the Cursor Block

Now we’ll write a PL/SQL block that uses a cursor to process rows conditionally.
SET SERVEROUTPUT ON;

DECLARE
-- Cursor declaration
CURSOR emp_cursor IS
SELECT EmpName, Salary
FROM EMPLOYEE
WHERE Salary > 50000;

-- Variables to store each row
v_name EMPLOYEE.EmpName%TYPE;
v_salary EMPLOYEE.Salary%TYPE;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ' | Salary: ' || v_salary);
END LOOP;
CLOSE emp_cursor;

END;
/

🎯 Output (on Oracle Live SQL console)
Employee: Asha | Salary: 52000
Employee: Sneha | Salary: 60000
Employee: Vikram | Salary: 80000

Trigger Example — AFTER INSERT on Students Table
✅ Step 1: Create Student and Audit Tables
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE STUDENT_AUDIT';
EXECUTE IMMEDIATE 'DROP TABLE STUDENTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE STUDENTS (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(50),
Course VARCHAR2(50)
);

CREATE TABLE STUDENT_AUDIT (
AuditID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID NUMBER,
StudentName VARCHAR2(50),
ActionDate DATE,
ActionType VARCHAR2(20)
);

✅ Step 2: Create the Trigger
CREATE OR REPLACE TRIGGER trg_student_insert
AFTER INSERT ON STUDENTS
FOR EACH ROW
BEGIN
INSERT INTO STUDENT_AUDIT (StudentID, StudentName, ActionDate, ActionType)
VALUES (:NEW.StudentID, :NEW.StudentName, SYSDATE, 'INSERT');
END;
/