See also:Why and if to use Pre-update and Pre-insert Triggers in Oracle Forms
pre-update Fires during thePost and Commit transactions process, before a row is updated in Oracle Forms. It fires once for each record, which is the marked for update.
The following example writes a row into anAudit Tableshowing old discount and new discount for a
given customer, including Timestampand 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 does this since the value of:Customer.Discount_Pct would be is the new value we ' re getting ready to commit and we w Ant to record for posterity the old and new values. We could use SELECT ... into but choose a explicit cursor for efficiency.
*/
OPEN Old_value;
FETCH old_value into Old_discount;
CLOSE Old_value;
/* If the old and current values is 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, which is the marked for insert. Example
This example assigns a Primary key field based on a Sequence Number, and then writes a row to 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 to the item in the Order record. Could Use SELECT ... into, but explicit cursor was 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 and 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
Pre-update and Pre-insert Trigger Examples for Oracle Forms
