CURSOR AND TRIGGER

My SQL Learning Journey: From Tables to Triggers
Hi Devs! I’m Varsha, and over the past few weeks, I’ve been diving deep into SQL using platforms like Oracle Live SQL and W3Schools. What started as curiosity quickly turned into a full-blown passion for understanding how data flows through relational databases. In this post, I’ll walk you through some of the key concepts I’ve learned—complete with code snippets and real examples.

Creating Tables Like a Pro
One of the first things I tackled was table creation. Here's a simple example:

sql
CREATE TABLE STUDENT (
StudentName VARCHAR(50),
StudentRollNo NUMBER(5),
StudentMarks NUMBER(5),
StudentGrade VARCHAR(2),
StudentResult VARCHAR(10)
);
Seeing “Table STUDENT created” in the output gave me a real sense of accomplishment!

Understanding Relationships with Foreign Keys
I then moved on to creating related tables:

sql
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50),
Age NUMBER
);

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50)
);

CREATE TABLE Enrollments (
EnrollmentID NUMBER PRIMARY KEY,
StudentID NUMBER,
CourseID NUMBER,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
This helped me understand how relational databases maintain integrity across tables.

Filtering Data with Date Ranges
I also learned how to filter records using BETWEEN and TO_DATE:

sql
SELECT auditid, studentname, actiondate
FROM student_audit
WHERE actiondate BETWEEN TO_DATE('01-OCT-2015','DD-MON-YYYY')
AND TO_DATE('05-OCT-2015','DD-MON-YYYY');
This query gave me a filtered view of student actions within a specific timeframe.

Auditing with Triggers
One of the coolest things I built was a trigger to audit changes in the STUDENTS table:

sql
CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT OR UPDATE OR DELETE ON STUDENTS
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO STUDENT_AUDIT (...) VALUES (...);
ELSIF UPDATING THEN
INSERT INTO STUDENT_AUDIT (...) VALUES (...);
ELSIF DELETING THEN
INSERT INTO STUDENT_AUDIT (...) VALUES (...);
END IF;
END;
This gave me a behind-the-scenes look at how changes are tracked in real-world systems.

Using Cursors in Procedures
Finally, I explored PL/SQL and created a procedure with a cursor:

sql
CREATE OR REPLACE PROCEDURE emp_proc IS
CURSOR emp_cursor IS SELECT empno, ename, sal FROM emp;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Empno: ' || emp_record.empno || ' Name: ' || emp_record.ename || ' Salary: ' || emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
/
EXEC emp_proc;
Running this felt like unlocking