WHEN Clause The WHEN clause is valid for rowlevel triggers only if present the

When clause the when clause is valid for rowlevel

This preview shows page 28 - 31 out of 36 pages.

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
Image of page 28
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
Image of page 29
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.
Image of page 30
Image of page 31

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture