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

COMPUTER SOFTWARE APPLICATION - CITS




              [REFERENCING OLD AS o NEW AS n]

              [FOR EACH ROW]
              WHEN (condition)
              DECLARE
              Declaration-statements
              BEGIN

              Executable-statements
              EXCEPTION
              Exception-handling-statements

              END;

           Where,
           •  CREATE  [OR  REPLACE]  TRIGGER  trigger_name  −  Creates  or  replaces  an  existing  trigger  with  the
              trigger_name.
           •  {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF
              clause is used for creating trigger on a view.
           •  {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
           •  [OF col_name] − This specifies the column name that will be updated.

           •  [ON table_name] − This specifies the name of the table associated with the trigger.
           •  [REFERENCING  OLD AS  o  NEW AS  n]  − This  allows  you  to  refer  new  and  old  values  for  various  DML
              statements, such as INSERT, UPDATE, and DELETE.
           •  [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being
              affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a
              table level trigger.

           •  WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only
              for row-level triggers.
           Example

           To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters −
           Select * from customers;
           +----+----------+-----+-----------+----------+
           | ID | NAME     | AGE | ADDRESS   | SALARY   |
           +----+----------+-----+-----------+----------+

           |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
           |  2 | Khilan   |  25 | Delhi     |  1500.00 |
           |  3 | kaushik  |  23 | Kota      |  2000.00 |
           |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
           |  5 | Hardik   |  27 | Bhopal    |  8500.00 |

           |  6 | Komal    |  22 | MP        |  4500.00 |
           +----+----------+-----+-----------+----------+
           The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or
           DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between
           the old values and new values −



                                                           100

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