Cursor and trigger in DBMS

Definition of Cursor in DBMS:-

A cursor in a Database Management System (DBMS) is a special control structure that enables traversal over the records in a result set obtained by executing a query. Unlike traditional SQL statements that operate on the entire set of rows simultaneously, a cursor allows row-by-row retrieval and processing of data. This makes it particularly useful when fine-grained, sequential manipulation of query results is required, such as performing conditional checks, applying calculations, or handling complex business logic on each individual row.
Cursor Example – Retrieving and Displaying Employee Records Where the Salary is Greater Than ₹50,000

Step 1: Create Employee Table

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

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

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

CREATE TABLE Student (
Student_ID NUMBER PRIMARY KEY,
Student_Name VARCHAR2(50),
Course VARCHAR2(50)
);
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
);

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;
/

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');

SELECT * FROM Student_Audit

Conclusion:-

Cursors provide the capability to process query results one row at a time based on specific conditions, enabling detailed and controlled data handling.
Triggers, on the other hand, help in automating actions such as maintaining logs or updating related tables, ensuring that certain tasks are carried out automatically without the need for manual execution.
Together, these features play a vital role in advanced database management by improving efficiency, consistency, and reliability.

Thank @santhoshnc Sir for his valuable guidance and continuous support in successfully completing this DBMS assignment.

dbms #sql #oracle #plsql #database #cursors #triggers #programming #assignment #learning