Conclusiones y recomendaciones:

Fires during the Post and Commit Transactions process, after a row is updated. It fires once for each row that is updated in the database during the commit process.

Usage Notes: Use a Post–Update trigger to audit transactions.

This Post-Update trigger writes the current record ID to the UPDATE_AUDIT table,along with a time stamp and the user who performed the update.

Example - 1 Begin

INSERT INTO update_audit (id, timestamp, who_did_it)VALUES ( :S_ORD.id, SYSDATE, USER ); End;

Example - 2 Begin

UPDATE S_ORD SET date_shipped = SYSDATE WHERE id = :S_ORD.id; IF SQL%NOTFOUND THEN

MESSAGE(’Record not found in database’); RAISE form_trigger_failure;

END IF; End;

Query Processing Triggers Uses 14. Pre – Select

Fires during Execute Query and Count Query processing, after Form Builder constructs the SELECT statement to be issued, but before the statement is actually issued. Note that the SELECT statement can be examined in a Pre-Select trigger by reading the value of the system variable SYSTEM.LAST_QUERY Fires after Form Builder has constructed the block SELECT statement based on the query conditions, but before it issues this statement

Use a Pre-Select trigger to prepare a query prior to execution against a non-ORACLE data source. 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 neworder.

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;

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;

15. On - Select

Fires when Form Builder would normally execute the open cursor, parse, and execute phases of a query, to identify the records in the database that match the current query criteria. On-Select replaces open cursor, parse, and execute phases.

Usage Notes

• Use an On-Select trigger to open and execute the database cursor. Specifically, use this trigger when you are retrieving data from a non-ORACLE data source. The On-Select trigger can be used in conjunction with the On-Fetch trigger to replace the processing that normally occurs in the EXECUTE_QUERY built-in subprogram.

• To perform the default Form Builder processing from this trigger, include a call to the SELECT_RECORDS built-in.

Example - 1

In the following example, the On-Select trigger is used to call a user exit, 'Query,' and a built-in subprogram, SELECT_RECORDS, to perform a query against a database.

Begin

IF Get_Application_Property(DATASOURCE) = 'DB2' THEN User_Exit ( 'Query' );

IF Form_Failure OR Form_Fatal THEN ABORT_QUERY;

END IF; ELSE

/* ** Perform the default Form Builder task of opening the query. */ Select_Records;

END IF; End;

16. Post-Select Trigger Description

Fires after Form Builder has constructed and issued the block SELECT statement, but before it fetches the records

The Post-Select trigger fires after the default selection phase of query processing, or after the successful execution of the On-Select trigger. It fires before any records are actually retrieved through fetch processing.

Usage Note:

Use the Post-Select trigger to perform an action based on the outcome of the Select phase of query processing such as an action based on the number of records that match the query criteria.

15. On – Fetch

Fires when Form Builder performs a fetch for a set of rows (You can use the

CREATE_QUERIED_RECORD built-in to create queried records if you want to replace default fetch processing.)

• On-Fetch continues to fire until:

– It fires without executing CREATE_QUERIED_RECORD. – The query is closed by the user or by ABORT_QUERY. – It raises FORM_TRIGGER_FAILURE.

The trigger will fire once for each record that is to be fetched. On–Fetch: DECLARE

j NUMBER := Get_Block_Property(blk_name, RECORDS_TO_FETCH); emprow emp%ROWTYPE;

BEGIN

FOR ctr IN 1..j LOOP

/* ** Try to get the next row. */

EXIT WHEN NOT MyPackage.Get_Next_Row(emprow); Create_Queried_Record;

:Emp.rowid := emprow.ROWID; :Emp.empno := emprow.EMPNO; :Emp.ename := emprow.ENAME; END LOOP;

IF form_fatal OR form_failure THEN raise form_trigger_failure; END IF;

END;

16. On – Count

Fires when Form Builder would usually perform default Count Query processing to determine the number of rows that match the query conditions

Fires when Form Builder would normally perform default Count Query processing to determine the number of rows in the database that match the current query criteria. When the

On-Count trigger completes execution, Form Builder issues the standard query hits message: FRM-40355: Query will retrieve <n> records.

Usage Notes

• Use an On-Count trigger to replace default Count Query processing in an application running against a non-ORACLE data source.

• To perform the default Form Builder processing from this trigger, include a call to the built-in. • If you are replacing default processing, you can set the value of the Query_Hits block property to

indicate the number of records in the non-ORACLE data source that match the query criteria. • Form Builder will display the query hits message (FRM-40355) even if the On-Count trigger fails to

set the value of the Query_Hits block property. In such a case, the message reports 0 records identified.

Example - 1

This example calls a user-named subprogram to count the number of records to be retrieved by the current query criteria, and sets the Query_Hits property appropriately.

DECLARE j NUMBER; BEGIN j := Recs_Returned('DEPT',Name_In('DEPT.DNAME')); Set_Block_Property('DEPT',QUERY_HITS,j); END; Example 2

/* ** Built-in: COUNT_QUERY ** Example: Display the number of records that will be retrieved ** by the current query. */

BEGIN

Count_Query; END;

Example 3

/* ** Built-in: COUNT_QUERY

** Example: Perform Form Builder count query hits processing. Decide whether to use this Built-in or a user ** exit based on a global flag setup at startup by the form, perhaps based on a parameter.

* Trigger: On-Count */ BEGIN

/* ** Check the global flag we set during form startup */ IF :Global.Using_Transactional_Triggers = 'TRUE' THEN

/* ** User exit returns query hits count back into the ** CONTROL.HITS item. */ User_Exit('my_count');

/* ** Deposit the number of query hits in the appropriate ** block property so Form Builder can display its normal ** status message. */

Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits); /* ** Otherwise, do the right thing. */

ELSE Count_Query; END IF; END; 17.On-Sequence-Number Trigger Description

Fires when Form Builder would normally perform the default processing for generating sequence numbers for default item values. Replaces the default series of events that occurs when Form Builder interacts with the database to get the next value from a SEQUENCE object defined in the database.

Usage Notes

• When a SEQUENCE is used as a default item value, Form Builder queries the database to get the next value from the SEQUENCE whenever the Create Record event occurs. Suppress or override this functionality with an On-Sequence-Number trigger. • To perform the default Form Builder processing from this trigger, call the

GENERATE_SEQUENCE_NUMBER built-in.

Example: /*

** Built–in: GENERATE_SEQUENCE_NUMBER

** Example: Perform Oracle Forms standard sequence number processing based on a global flag setup at ** startup by the form, perhaps based on a parameter.

** Trigger: On–Sequence–Number */ BEGIN

IF :Global.Using_Transactional_Triggers = ’TRUE’ THEN User_Exit(’my_seqnum seq=EMPNO_SEQ’);

/* ** Otherwise, do the right thing. */ ELSE Generate_Sequence_Number; END IF; END; 18. On-Check-Unique Trigger