Page 113 - CITS - Computer Software Application -TT

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