CURSOR + TRIGGER

Cursor example
Step 1: Create a sample table (Employee)

Step 2: Insert some records.

Step 3: Write Cursor Program.

TRIGGER EXAMPLE STEP BY STEP

Step 4: Create an audit table.

Step 5: Create a trigger.

Step 6: Test the trigger.

Now check
SELECT * FROM Employee_Audit;
Step 7:A cursor was used to fetch and display employee records satisfying a condition, while a trigger was created to automatically log insert operations into an audit table. This demonstrates how cursors help in handling query results row by row and how triggers ensure automatic enforcement of business rules. Together, they show the importance of procedural extensions in SQL for effective database management.

-- Step 1: Create Employee table
CREATE TABLE Employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER
);

-- Step 2: Insert sample records
INSERT INTO Employee VALUES (1, 'Rahul', 60000);
INSERT INTO Employee VALUES (2, 'Priya', 45000);
INSERT INTO Employee VALUES (3, 'Kiran', 75000);
COMMIT;

-- Step 3: Cursor Program (fetch employees with salary > 50000)
DECLARE
v_id Employee.emp_id%TYPE;
v_name Employee.emp_name%TYPE;
v_sal Employee.salary%TYPE;

CURSOR c_emp IS
SELECT emp_id, emp_name, salary FROM Employee WHERE salary > 50000;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id, v_name, v_sal;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' | ' || v_name || ' | ' || v_sal);
END LOOP;
CLOSE c_emp;
END;
/

-- Step 4: Create Employee_Audit table (for trigger)
CREATE TABLE Employee_Audit (
emp_id NUMBER,
action VARCHAR2(20),
action_time DATE
);

-- Step 5: Create Trigger to log inserts
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Audit VALUES (:NEW.emp_id, 'INSERT', SYSDATE);
END;
/

-- Step 6: Test the trigger
INSERT INTO Employee VALUES (4, 'Sneha', 55000);
COMMIT;

-- Step 7: Check audit log
SELECT * FROM Employee_Audit;