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)