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

COMPUTER SOFTWARE APPLICATION - CITS





              CREATE OR REPLACE TRIGGER display_salary_changes
              BEFORE DELETE OR INSERT OR UPDATE ON customers

              FOR EACH ROW
              WHEN (NEW.ID > 0)
              DECLARE
                 sal_diff number;
              BEGIN

                 sal_diff := :NEW.salary  - :OLD.salary;
                 dbms_output.put_line(‘Old salary: ‘ || :OLD.salary);
                 dbms_output.put_line(‘New salary: ‘ || :NEW.salary);
                 dbms_output.put_line(‘Salary difference: ‘ || sal_diff);
              END;

              /




           When the above code is executed at the SQL prompt, it produces the following result −
           Trigger created.
           The following points need to be considered here −
           •  OLD and NEW references are not available for table-level triggers, rather you can use them for record-level
              triggers.
           •  If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers
              can query the table or change it again only after the initial changes are applied and the table is back in a
              consistent state.
           •  The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE
              operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE
              DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.
           Triggering a Trigger
           Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will
           create a new record in the table −
              INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
              VALUES (7, ‘Kriti’, 22, ‘HP’, 7500.00 );

           When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be
           fired and it will display the following result −

           Old salary:
           New salary: 7500
           Salary difference:
           Because this is a new record, old salary is not available and the above result comes as null. Let us now perform
           one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in
           the table −









                                                           101

                              CITS : IT&ITES - Computer software application - Lesson 18 - 36
   109   110   111   112   113   114   115   116   117   118   119