Triggers Vs Cursors

 

Triggers are named PL/SQL blocks

Cursors are temporary work areas

Triggers are stored in databases

Cursors are not stored independently in the database

Triggers can be invoked automatically

User can create cursor both implicitly and explicitly

Triggers cannot take parameters

Cursors can take parameters

Triggers are used to enforce data in dignity rules

Cursors are used to process the result of query

Trigger

A trigger is a stored block of SQL code that runs automatically when a particular event happens on a table, such as INSERT, UPDATE or DELETE.

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employee
FOR EACH ROW
SET NEW.updated_on = NOW();

Uses: keeping logs, checking data before it is saved, and updating related tables automatically.

Cursor

A cursor is a pointer used inside a stored procedure to process the rows returned by a query one row at a time.

Steps to use a cursor: DECLARE the cursor, OPEN it, FETCH rows one by one in a loop, and CLOSE it.

Differences

  • Execution: a trigger runs automatically on an event; a cursor is used explicitly by the programmer.
  • Purpose: a trigger enforces rules or actions on data changes; a cursor processes query results row by row.
  • Where defined: a trigger is attached to a table; a cursor is declared inside a stored procedure or block.
  • Events: triggers respond to INSERT, UPDATE, DELETE; cursors do not depend on events.

Important Questions

Q. What is a trigger?
Ans. A trigger is a set of SQL statements that executes automatically when an INSERT, UPDATE or DELETE event occurs on a table.

Q. Write the four steps of using a cursor.
Ans. Declare, Open, Fetch and Close.

Q. Write one difference between a trigger and a cursor.
Ans. A trigger runs automatically on a table event, while a cursor must be explicitly declared and used to process rows one at a time.