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 systems125NotesAmity Directorate of Distance & Online EducationWHEN 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)BEGINRaise_application_error(-20001, ‘Amount can not be less than 100);END;Fig. 6.39Trigger 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 eventsFor 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 ROWDELETE ON emp FOR EACH ROWDECLAREmode_op CHAR(1);BEGINIF INSERTING THENMode_op: = ‘I’;ELSIF UPDATING THENMode_op: = ‘U’;ELSEMode_op: = ‘D’; // If deletingENDIF;INSERT INTO emp_log VALUES( USER, SYSDATE, mode_op);END;Fig. 6.40Dropping a TriggerA 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
126Data Base Management systemsNotesAmity Directorate of Distance & Online EducationDROP 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.