Cursor and Trigger in DBMS

cursor in dbms

In DBMS, a cursor is a pointer that allows row-by-row processing of the result set of a query, enabling traversal, retrieval, and manipulation of records one at a time.

example: the employees with salary more than ₹50,000 are Bob, Charlie, and Ethan.

Step 1: Create Employee Table

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);

Step 2: Insert the Sample Data

INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 55000),
(4, 'Diana', 48000),
(5, 'Ethan', 75000);

Step 3: Declare and Process the 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:

• A cursor is used to process query results row by row, unlike normal SQL which works on the whole set at once.

• The process involves declaring the cursor with a SELECT query, opening it, fetching rows one at a time, and finally closing and deallocating it.

• Cursors are useful when you need to retrieve, display, or update individual records sequentially (e.g., showing employees with salary > ₹50,000).

Trigger in dbms

In DBMS, a trigger is a special type of stored procedure that is automatically executed (fired) in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

Step 1: Create the Student Table

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
Age INT,
Gender VARCHAR(10),
Department VARCHAR(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 TRIGGER trg_After Insert_Student
ON Student
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName, ActionType, ActionTime)
SELECT
StudentID,
StudentName,
'INSERT',
GETDATE()
FROM Inserted; -- 'Inserted' holds newly added rows
END;

Step 4: Test Trigger

INSERT INTO Student (StudentID, StudentName, Age, Gender, Department)
VALUES (7, 'Grace', 20, 'Female', 'Computer Science');

Step 5: Verify Audit Table

SELECT * FROM Student_Audit;

Conclusion:

• Triggers are automatic procedures that execute when an event (INSERT, UPDATE, DELETE) occurs on a table.

• They are useful for maintaining logs, enforcing rules, and ensuring data integrity without manual intervention.

• In our example, the AFTER INSERT trigger on the Student table successfully recorded every new student entry into the Student_Audit table, proving how triggers help in tracking changes automatically.

Thank You @santhoshnc sir for guiduing us and encouraging us by giving this as assignment.