See also: Why And When To Use Pre-Update and Pre-Insert Triggers In Oracle Forms
Pre-Update Fires during the Post and CommitTransactions process, before a row is updated in Oracle Forms. It fires once for each record that is marked for update.
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;
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;
Pre-Insert trigger
Pre-Insert Fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert.
Example
This example assigns a primary key field based on a sequence number, and then writes a row into an
auditing table, flagging creation of a new order.
This example assigns a primary key field based on a sequence number, and then writes a row into an
auditing table, flagging creation of a new order.
DECLARE
CURSOR next_ord IS SELECT orderid_seq.NEXTVAL FROM dual;
BEGIN
/* Fetch the next sequence number from the explicit cursor directly into the item in the Order record. Could use SELECT...INTO, but explicit cursor is more efficient. */
OPEN next_ord;
FETCH next_ord INTO :Order.OrderId;
CLOSE next_ord;
/*
Make sure we populated a new order id ok...
*/
IF :Order.OrderId IS NULL THEN
Message(’Error Generating Next Order Id’);
RAISE Form_trigger_Failure;
END IF;
/*
Insert a row into the audit table
*/
INSERT INTO ord_audit( orderid, operation, username, timestamp)
VALUES ( :Order.OrderId,’New Order’,USER, SYSDATE );
END;
CURSOR next_ord IS SELECT orderid_seq.NEXTVAL FROM dual;
BEGIN
/* Fetch the next sequence number from the explicit cursor directly into the item in the Order record. Could use SELECT...INTO, but explicit cursor is more efficient. */
OPEN next_ord;
FETCH next_ord INTO :Order.OrderId;
CLOSE next_ord;
/*
Make sure we populated a new order id ok...
*/
IF :Order.OrderId IS NULL THEN
Message(’Error Generating Next Order Id’);
RAISE Form_trigger_Failure;
END IF;
/*
Insert a row into the audit table
*/
INSERT INTO ord_audit( orderid, operation, username, timestamp)
VALUES ( :Order.OrderId,’New Order’,USER, SYSDATE );
END;
Follow to get notifications for more tutorials with source code, thanks.
Pre-insert and Pre-update in Oracle Forms
Reviewed by Rasa on
Mar 24
Rating:
5