DBMS : Cursor & Trigger

#dbms #oracle #cursors #tutorial

1️⃣ Cursor in DBMS

Definition:
A cursor is a database object used to retrieve and process rows from a result set one at a time. It allows row-by-row processing of query results, unlike normal SQL queries that work on the entire set at once.

Cursor Example – Display Employees with Salary > ₹50,000
_
_Step 1: Create Employee Table

CREATE TABLE Employee (
    Emp_ID NUMBER PRIMARY KEY,
    Emp_Name VARCHAR2(50),
    Salary NUMBER
);

Step 2: Insert Sample Data

INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (1, 'Ramesh', 60000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (2, 'Suresh', 45000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (3, 'Anita', 75000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (4, 'Kavya', 50000);

Step 3: Create and Process Cursor

DECLARE
    CURSOR emp_cursor IS
        SELECT Emp_Name, Salary FROM Employee WHERE Salary > 50000;
    v_EmpName Employee.Emp_Name%TYPE;
    v_Salary Employee.Salary%TYPE;
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;
/

Explanation:

The cursor emp_cursor selects employees earning more than 50,000.

DBMS_OUTPUT.PUT_LINE prints each employee’s name and salary.

2️⃣ Trigger in DBMS

Definition:
A trigger is a stored procedure that automatically executes in response to certain events on a table or view.

trigger Example – AFTER INSERT on Students Table

Step 1: Create Students Table

CREATE TABLE Students (
    Student_ID NUMBER PRIMARY KEY,
    Student_Name VARCHAR2(50),
    Course VARCHAR2(50)
);

_Step 2: Create Student_Audit Table
_

CREATE TABLE Student_Audit (
    Audit_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Student_ID NUMBER,
    Student_Name VARCHAR2(50),
    Action VARCHAR2(50),
    Action_Time TIMESTAMP
);

Step 3: Create 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, Action, Action_Time)
    VALUES (:NEW.Student_ID, :NEW.Student_Name, 'INSERT', SYSTIMESTAMP);
END;
/

Explanation:

Trigger automatically logs new students into Student_Audit.

:NEW references the inserted row, and SYSTIMESTAMP captures insertion time.

Step 4: Test Trigger

INSERT INTO Students (Student_ID, Student_Name, Course) VALUES (1, 'Ravi', 'Computer Science');
INSERT INTO Students (Student_ID, Student_Name, Course) VALUES (2, 'Meena', 'Electrical Engineering');