Page 112 - CITS - Computer Software Application -TT
P. 112

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
   107   108   109   110   111   112   113   114   115   116   117