Pre-Update and Pre-Insert Trigger Examples For Oracle Forms

jqkyp

DECLARE　　old_discount NUMBER;
　　new_discount NUMBER := :Customer.Discount_Pct;
　　oper_desc VARCHAR2(80);
　　CURSOR old_value IS SELECT discount_pct FROM customer
　　WHERE CustId = :Customer.CustId;
　　BEGIN
　　/*
　　Fetch the old value of discount percentage from the database by CustomerId. We need to do this since the  value of :Customer.Discount_Pct will be the new value we’re getting ready to commit and we want to record for posterity the old and new values. We could use SELECT...INTO but choose an explicit cursor for efficiency.
　　*/
　　OPEN old_value;
　　FETCH old_value INTO old_discount;
　　CLOSE old_value;
　　/*  If the old and current values are different, then we need to write out an audit record */
　　IF old_discount <> new_discount THEN
　　/* Construct a string that shows the operation of Changing the old value to the new value. e.g.’Changed Discount from 13.5% to 20%’
　　*/
　　oper_desc := ’Changed Discount from ’||
　　TO_CHAR(old_discount)||’% to ’||
　　TO_CHAR(new_discount)||’%’;
　　/*
　　Insert the audit record with timestamp and user
　　*/
　　INSERT INTO cust_audit( custid, operation, username, timestamp )
　　VALUES ( :Customer.CustId,oper_desc,USER,SYSDATE );
　　END IF;
　　END;