Page 112 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

SELECT employee_name, salary
FROM employees;
OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @employee_name, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process the current employee (e.g., print or update)
PRINT CONCAT(‘Employee: ‘, @employee_name, ‘, Salary: ‘, @salary);

-- Fetch the next employee
FETCH NEXT FROM emp_cursor INTO @employee_name, @salary;
END;
CLOSE emp_cursor;
In this example, the cursor iterates through the result set of the query, fetching the employee name and salary for
each row and then processing them within a loop. Cursors provide a flexible mechanism for handling individual
rows in a result set within procedural code
Creating Triggers

In this chapter, we will discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed
or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following
events −
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is associated.
Benefits of Triggers
Triggers can be written for the following purposes −

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables

• Imposing security authorizations
• Preventing invalid transactions
The syntax for creating a trigger is −
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name

99

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