WHEN Clause:
The WHEN clause is valid for row_level triggers only if present, the trigger
body will be executed only for those rows that meet the condition specified by the
Data Base Management systems
125
Notes
Amity Directorate of Distance & Online Education
WHEN clause. WHEN clause is a BOOLEAN expression, which will be evaluated for
each row. The :new and :old records can be referenced inside the condition as well.
For example, if in banking system, amount debited or credited is less than Rs. 100
then it should not allow the transaction.
CREATE OR REPLACE TRIGGER check_trans BEFORE INSERT ON transaction
FOR EACH ROW WHEN (:new.amount<100)
BEGIN
Raise_application_error(-20001, ‘Amount can not be less than 100);
END;
Fig. 6.39
Trigger Predicates (INSERTING, UPDATING, DELETING)
Till now we have used only one triggering even (INSERT, UPATE or DELETE) for
a trigger but if we have to take different action based on the different events in a single
trigger then we can use a trigger predicates. These are the Boolean functions that are
used to determine the events
For example:
Consider a table emp_log which is used to keep the track of all the activities
(insert, update, delete) on emp table by any user. The structure of this table is as follows:
Emp_log (Person, Modi_date, Operation_mode)
Person attributes stores the name of the user who is using emp table. Modi_date
is the date of modification done by any user on emp table and operation_mode is the
activity (insert, update or delete) performed on emp table. Now the trigger for storing this
information after any database related activity is as follows:
CREATE OR REPLACE TRIGGER emp_log_trigger AFTER INSERT OR UPDATE
OR DELETE ON emp FOR EACH ROW
DELETE ON emp FOR EACH ROW
DECLARE
mode_op
CHAR(1);
BEGIN
IF INSERTING THEN
Mode_op: = ‘I’;
ELSIF UPDATING THEN
Mode_op: = ‘U’;
ELSE
Mode_op: = ‘D’;
// If deleting
ENDIF;
INSERT INTO emp_log VALUES( USER, SYSDATE, mode_op);
END;
Fig. 6.40
Dropping a Trigger
A trigger once created cannot be altered. If the user wants to change the trigger then
he has to drop it first and then recreate it or he can create trigger by using OR REPLACE
option which will replace the previous trigger.
The syntax for dropping the trigger is:
DROP TRIGGER <trigger_name>;
For example, to drop the check_trans trigger, the syntax is
126
Data Base Management systems
Notes
Amity Directorate of Distance & Online Education
DROP TRIGGER check_trans.
INSTEAD OF trigger: These are the triggers that are defined on a view rather than
on a table. Such triggers can be used to overcome the restrictions placed by Oracle on
any view, which is deemed to be non-updateable. If a view is based on joining of two or
more tables ten DML operations (Insert, Update and Delete) are not possible against that
view. Only columns belonging to one tale can be updated in a single statement and the
view must be key preserved, meaning that the join key columns may not be updated. To
overcome this events as their table counterparts, that is INSERT, UPDATE, and DELETE.
