DEV Community

Cover image for Cursor and Trigger in DBMS
Jerlin vanessa Vincent paul
Jerlin vanessa Vincent paul

Posted on

Cursor and Trigger in DBMS

When working with databases, sometimes we need to process records row by row (using Cursors) or automatically respond to events (using Triggers).

In this tutorial, we’ll:

✅ Create a Cursor that fetches employees with a salary > 50,000

✅ Build an AFTER-INSERT Trigger to maintain a student audit log

🔹 Cursor

A cursor is a pointer that lets you process query results row by row instead of all at once. Useful when applying conditions or logic to each record.

Step 1: Employee Cursor Example

Let’s create a cursor to display employee names with salary > 50,000.

Step i: Create Employee Table

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

Step ii: Insert Sample Data

INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (1, 'Renner', 60000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (2, 'Samuel', 45000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (3, 'Ana',
75000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (4, 'Kylie', 50000);

Step iii: Cursor Implementation

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

🔹 Trigger

A trigger is a stored program that automatically runs when a specific event occurs (like INSERT, UPDATE, or DELETE).

Step 2:AFTER INSERT Trigger

We’ll now create a Students table and a Students_Audit table to keep track of new registrations.

Step i: Create Students Table

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

Step ii: Create Students_Audit Table

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

Step iii: Create AFTER INSERT Trigger(Trigger Implementation)

CREATE OR REPLACE TRIGGER trg_after_student_insert
AFTER INSERT ON Students2
FOR EACH ROW
BEGIN
INSERT INTO Students_Audit (Student_ID, Student_Name, Action, Action_Time)
VALUES (:NEW.Student_ID, :NEW.Student_Name, 'INSERT', SYSTIMESTAMP);
END;
/

Test the Trigger

INSERT INTO Students2 (Student_ID, Student_Name, Course) VALUES (1, 'Renner', 'Computer Science');
INSERT INTO Students2 (Student_ID, Student_Name, Course) VALUES (2, 'Martin', 'Mechanical Engineering');

Step iv: Verify Audit Table

SELECT * FROM Students_Audit;

🚀 Wrap Up

In this tutorial, we learned:

Cursor → Process query results row by row

Trigger → Automatically log student registrations after insert

These features add power and automation to SQL programming!

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

Top comments (0)