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

COMPUTER SOFTWARE APPLICATION - CITS




           Stored procedures are a valuable feature in SQL that allows you to encapsulate complex database operations,
           enforce security, and promote code reusability. They are widely used in database-driven applications and are an
           important part of database management and development.

             Creating Trigger, Creating Cursor, Using Cursor

           Introduction to Triggers

           Certainly! Triggers in the context of databases refer to special types of stored procedures that automatically execute
           in response to specific events on a particular table or view. These events typically involve data manipulation
           operations, such as INSERT, UPDATE, DELETE, or even certain schema-level events like CREATE, ALTER, or
           DROP.
           “A trigger is an automated code segment, acting as a procedure, that is executed in response to specific events
           occurring in a table or view within a database. In contrast, a cursor is a control structure utilized in databases for
           traversing through records. It’s noteworthy that a cursor can be declared and employed within the context of a
           trigger “
           Key Concepts:
           1  Events
              •  INSERT: Triggered after a new row is added to the table.

              •  UPDATE: Triggered after one or more existing rows are modified.
              •  DELETE: Triggered after one or more rows are removed from the table.
           2  Timing
              •  BEFORE Triggers: Executed before the triggering event, allowing modification of the data before it is
                 actually written to the database.
              •  AFTER Triggers: Executed after the triggering event has occurred.
           3  Row-Level and Statement-Level Triggers
              •  Row-Level Triggers: Executed once for each row affected by the triggering event.

              •  Statement-Level Triggers: Executed once for each triggering event, regardless of the number of rows
                 affected.
           Use Cases

           1  Data Validation
              •  Ensure that certain conditions are met before allowing data changes.
           2  Enforcing Business Rules
              •  Implement complex business logic or rules automatically.
           3  Audit Trails
              •  Log changes made to a table for auditing purposes.

           4  Cascade Operations
              •  Automatically perform additional operations on other tables when a specified event occurs.
           5  Synchronization
              •  Keep multiple tables in sync by triggering actions in response to changes in one table.

           Syntax (for an AFTER INSERT Trigger):
           CREATE TRIGGER trigger_name
           AFTER INSERT ON table_name





                                                           96

                              CITS : IT&ITES - Computer software application - Lesson 18 - 36
   104   105   106   107   108   109   110   111   112   113   114