Page 110 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

FOR EACH ROW
BEGIN
-- Trigger logic here
END;

• trigger_name: The name you assign to the trigger.
• AFTER INSERT ON table_name: Specifies the timing and event that trigger the execution.
• FOR EACH ROW: Indicates that the trigger will be executed once for each row affected by the triggering event.
• BEGIN...END: The block where you define the logic of the trigger.

Example:
Let’s consider a simple example where we want to create an audit trail for an employees table:
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW

BEGIN
INSERT INTO employee_audit (employee_id, action, timestamp)
VALUES (NEW.employee_id, ‘INSERT’, NOW());
END;
In this example, every time a new row is inserted into the employees table, the trigger logs the action into an
employee_audit table with details like the employee ID, the action performed (INSERT), and the timestamp.
Triggers are powerful tools, but they should be used with caution to avoid unintended consequences and to
ensure they do not negatively impact database performance.

Introduction to Cursor

A cursor in the context of databases is a programming construct or a database object that enables the traversal
and manipulation of records in a result set. It provides a mechanism for iterating over a set of rows returned by
a SQL query. Cursors are often used in procedural languages, such as PL/SQL or T-SQL, to perform operations
on a row-by-row basis.
There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.

1 Implicit Cursors, often referred to as the Default Cursors of SQL Server, are automatically assigned by SQL
Server when users execute Data Manipulation Language (DML) operations. These cursors are generated by
the system without explicit declaration by the user.
2 explicit cursors: Users create explicit cursors when needed. These cursors are specifically crafted by users
for the purpose of retrieving data from a table in a row-by-row manner.

How To Create Explicit Cursor?
1 Declare Cursor Object
Syntax:
DECLARE cursor_name CURSOR FOR
SELECT * FROM table_name

Query:
DECLARE s1 CURSOR FOR
SELECT * FROM studDetails

97

CITS : IT&ITES - Computer software application - Lesson 18 - 36