Cursor and Trigger in DBMS

#sql #coding #database #learning

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.