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.