SQL Cursors & Triggers

SQL Cursors & Triggers: What They Are and When to Use Them

In SQL, you often run set-based operations (e.g. SELECT, UPDATE, DELETE) over many rows at once. But what if you need to treat each row individually? Or automatically react to changes in your data? That’s where Cursors and Triggers enter the picture.

Let’s dive into each, see examples, and explore when to use one over the other.

1. Cursors: Row-by-Row Processing

A cursor is like a pointer that lets you step through the result set of a query, one row at a time. In scenarios where you need procedural logic or conditional steps per row, a cursor can be very useful.

Example

Suppose you have a table of employees and you want to process those whose salary is greater than 50,000:

DECLARE @empId INT, @salary DECIMAL(10,2)

DECLARE emp_cursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees
WHERE Salary > 50000

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @empId, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Example logic: give a bonus or update something
PRINT 'Employee ' + CAST(@empId AS VARCHAR) + ' has salary ' + CAST(@salary AS VARCHAR)

FETCH NEXT FROM emp_cursor INTO @empId, @salary;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Here, the cursor:

Declares which result set to iterate over.

Opens it.

Fetches rows one by one.

Lets you run custom logic per row.

Finally closes and deallocates the resource.

When to Use Cursors

Use a cursor when:

You need to perform row-level operations that can’t be done easily in a set operation.

Your logic depends on the value from the previous row, or you need sequential access.

You must mix complex procedural logic with SQL data.

However, be cautious: cursors can be slower and more resource-intensive compared to pure set-based queries, especially on large datasets.

1. Triggers: Automate Actions on Data Changes

A trigger is a database object that defines automatic behavior when certain events occur — e.g. INSERT, UPDATE, or DELETE. Once you set it up, it “listens” for those events and runs code accordingly.

Example: AFTER INSERT Trigger for Auditing

Let’s say you want to audit insertions into a Students table by copying new rows into a separate Student_Audit table:

-- Audit table
CREATE TABLE Student_Audit (
StudentID INT,
Name VARCHAR(100),
InsertedOn DATETIME DEFAULT GETDATE()
);

-- Trigger definition
CREATE TRIGGER trg_AfterInsert_Students
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (StudentID, Name)
SELECT i.StudentID, i.Name
FROM inserted AS i;
END;

Now, whenever a new student record is inserted into Students, the trigger will fire automatically and log the inserted data into Student_Audit.

When to Use Triggers

Triggers are suited when:

You want automatic logging, auditing, or enforcing rules at the database layer.

You want to centralize certain logic so that it can’t be bypassed (e.g., always enforce referential integrity or soft deletes).

You need to react to changes in data without requiring the application to do so.

Be careful, though: triggers can introduce complexity and unexpected side-effects if not managed carefully (e.g., cascading triggers, nested triggers, or impacting performance).

1. Summary & Best Practices Feature Purpose Use Case Example Cursor Process rows one at a time Complex per-row logic, sequential ops Trigger Automate reaction to data changes Auditing, enforcing business rules

Key takeaways:

Use cursors when you absolutely need fine-grained control on each record.

Use triggers when you want automatic reactions at the database level.

Prefer set-based SQL operations when possible, for performance and readability.

Monitor and test performance, especially for triggers or cursor loops on large tables.