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

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