Cursor + Trigger

Step 1: Create the Required Tables
To begin, we need to set up the database tables involved in the cursor and trigger tasks. This includes:

• Employee table: For storing employee details like name and salary (used for the cursor).
• Students table: For storing student details (used for the trigger).
• Student_Audit table: For logging audit entries when a new student is added.

CREATE TABLE Employee (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    salary NUMBER
);

CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(100)
);

CREATE TABLE Student_Audit (
    student_id NUMBER,
    registration_date DATE,
    action VARCHAR2(10)
);

Step 2: Insert Sample Data into the Employee Table
Next, populate the Employee table with sample data to test the cursor. I added four employees with salaries above and below 50,000 for variety.

INSERT INTO Employee (id, name, salary) VALUES (1, 'John Doe', 60000);
INSERT INTO Employee (id, name, salary) VALUES (2, 'Jane Smith', 40000);
INSERT INTO Employee (id, name, salary) VALUES (3, 'Alice Johnson', 55000);
INSERT INTO Employee (id, name, salary) VALUES (4, 'Bob Brown', 45000);
COMMIT;

Step 3: Implement and Execute the Cursor
The cursor task is to display employee names where salary > 50,000 from the Employee table.

DECLARE
    CURSOR emp_cursor IS
        SELECT name FROM Employee WHERE salary > 50000;
    emp_name VARCHAR2(100);
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_name);
    END LOOP;
    CLOSE emp_cursor;
END;

Step 4: Create the AFTER INSERT Trigger
Now, create the trigger on the Students table to automatically log insertions into Student_Audit.

CREATE OR REPLACE TRIGGER student_audit_trigger
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit (student_id, registration_date, action)
    VALUES (:NEW.student_id, SYSDATE, 'INSERT');
END;

**
Output:**

Step 5: Insert a New Student to Test the Trigger
Insert a sample student to activate the trigger.

INSERT INTO Students (student_id, name) VALUES (1, 'New Student');
COMMIT;

Output:
text

Step 6: Verify the Audit Log
Finally, query the Student_Audit table to see the trigger's effect.

SELECT * FROM Student_Audit;