A script-enabled browser is required for this page to function properly.

Pre-Update Trigger

Description

Fires during the Post and Commit Transactions process, before a row is updated. It fires once for each record that is marked for update.

Definition Level form or block

Legal Commands

SELECT statements, DML statements (DELETE, INSERT, UPDATE), unrestricted Built-ins

Enter Query Mode no

Usage Notes

Use a Pre-Update trigger to audit transactions.

On Failure

Oracle Forms performs the following steps when the Pre-Update trigger fails:

Fires In

Post and Commit Transactions

Pre-Update Trigger Example

The following example writes a row into an Audit Table showing old discount and new discount for a given customer, including timestamp and username making the change.

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;