DEV Community

Cover image for Cursor and Trigger in DBMS
Jaswant Karun
Jaswant Karun

Posted on

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 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.

Definition of Trigger in DBMS:-

  • A trigger in a Database Management System (DBMS) is a special type of stored procedure that is automatically invoked or executed by the system whenever a specific event, such as an insertion, update, or deletion, occurs on a particular table or view. Triggers are mainly used to maintain data integrity, enforce business rules, and keep audit logs without requiring explicit calls from the user.

Trigger Example – Executing an AFTER INSERT Operation on the 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');

Step 5: Verify Audit Table

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

Top comments (0)