Cursor And Trigger In DBMS

Sql Database

Tutorial

STEP:1 Create A Sample Table
CREATE TABLE Employee (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER
);

Step 2: Insert the Record
INSERT INTO Employee VALUES (1, 'John Doe', 60000);
INSERT INTO Employee VALUES (2, 'Jane Smith', 48000);
INSERT INTO Employee VALUES (3, 'Emily Davis', 52000);
COMMIT;

Step 3: Write A cursor Program
CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (audit_id, student_id, action, log_time)
VALUES (student_audit_seq.NEXTVAL, :NEW.student_id, 'INSERT', SYSTIMESTAMP);
END;
/

TRIGGER EXAMPLES

Step 4: Create An Audit Table
CREATE TABLE Students (
student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(50),
course VARCHAR2(30)
);

Step:5 Create A Trigger
CREATE TABLE Student_Audit (
audit_id NUMBER PRIMARY KEY,
student_id NUMBER,
action VARCHAR2(20),
log_time TIMESTAMP
);

Step 6: Test A Trigger
CREATE SEQUENCE student_audit_seq START WITH 1 INCREMENT BY 1;

Conclusion
This tutorial showed how to use a cursor to process conditional data and how to automate audit logging using an AFTER INSERT trigger in Oracle SQL. Try running these examples yourself on Oracle Live SQL, and customize the logic for your own applications!