CURSORS AND TRIGGERS

Simulating Cursors and Using Triggers in SQL

Cursors and triggers are powerful tools in SQL we’ll explore both with practical examples.

1️⃣ Employee Table Setup

Let’s create an Employee table with sample data:

CREATE TABLE Employee (
    emp_id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
);

INSERT INTO Employee(emp_id, name, salary) VALUES
(1, 'Alice', 60000),
(2, 'Bob', 45000),
(3, 'Charlie', 75000),
(4, 'David', 50000);

2️⃣ Simulating a Cursor in Programiz

In traditional SQL, cursors allow row-by-row processing. Since Programiz doesn’t support cursors, we can use a SELECT statement with a condition to get the same result.

SELECT name 
FROM Employee 
WHERE salary > 50000;

Output:

Alice
Charlie

This effectively simulates a cursor that processes only employees with salary greater than 50,000.

3️⃣ Trigger Example: AFTER INSERT

Triggers automatically execute actions in response to table events.

Scenario

Whenever a new student is added to the Students table, we want to log the registration in a Student_Audit table.

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    name TEXT,
    dept TEXT
);

CREATE TABLE Student_Audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER,
    action TEXT,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

AFTER INSERT Trigger

SQLite supports triggers like this:

CREATE TRIGGER after_student_insert
AFTER INSERT ON Students
BEGIN
    INSERT INTO Student_Audit(student_id, action)
    VALUES (NEW.student_id, 'Student Registered');
END;

Test the Trigger

INSERT INTO Students(student_id, name, dept)
VALUES (1, 'Alice', 'CSBS');

SELECT * FROM Student_Audit;

Output:

audit_id	student_id	action	action_time
1	1	Student Registered	2025-10-01 09:00:00

The trigger automatically logs the registration — no manual step needed.

4️⃣ Key Takeaways

Feature	How to Use in Programiz / SQLite
Cursor	Use SELECT ... WHERE for conditional row processing.
Trigger	Automate actions on INSERT, UPDATE, DELETE.

Even without traditional cursors, you can process data row-wise using conditions. Triggers help maintain audit logs or automatic actions.

Conclusion

By simulating cursors and using triggers, you can:

• Filter and process rows based on conditions.
• Automatically respond to database events like inserts.

This makes your database smarter and more automated, even in **SQLite-based editors

Tip: For more advanced row-by-row processing, you can combine SQLite queries with application code (Python, Java, etc.) to simulate full cursor behavior.