20 Transaction Processing Copyright Oracle Corporation 1999 All

20 Transaction Processing Copyright Ó Oracle Corporation, 1999. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: •

Transaction Processing FORM A Action Edit Transaction (Begin) Save Block#1 UPDATE Table 1 New

Transaction Processing Transaction processing includes two phases: • Post: – Writes record changes to

The Commit Sequence of Events Validate the form Pre-Commit 2 Validate the block More

The Commit Sequence of Events INSERT 1 UPDATE More records? Copy value from item

Characteristics of Commit Triggers • Pre-Commit: Fires once if form changes are made or

Characteristics of Commit Triggers • Post-Forms-Commit: Fires once even if no changes are made

Commit Triggers Uses Pre-Commit Check user authorization; set up special locking Pre-Delete Journaling; implement

Commit Triggers Uses On-Insert/Update/Delete Replace default block DML statements Post-Forms-Commit Check complex multirow constraints

Life of an Update Query 20 Column Rollback Locked Data 20 30 20 [Save]

Delete Validation • • Pre-Delete trigger Final checks before row deletion DECLARE CURSOR C

Assigning Sequence Numbers SELECT S_ORD_ID. nextval INTO : S_ORD. id FROM SYS. dual; Insert

Keeping an Audit Trail • • Write changes to nonbase tables. Gather statistics on

Testing the Result of Trigger DML • • • SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT UPDATE S_ORD

DML Statements Issued During Commit Processing INSERT INTO base_table (base_column, . . . )

DML Statements Issued During Commit Processing Rules: • • • DML statements may fire

Overriding Default Transaction Additional transactional triggers: Trigger Do-the-Right-Thing Built-in On-Check-Unique CHECK_RECORD_UNIQUENESS On-Column-Security ENFORCE_COLUMN_SECURITY On-Commit

Overriding Default Transactional triggers for logging on and off: Trigger Do-the-Right-Thing Built-in Pre-Logon -

Running with Data Sources Other than Oracle • Three ways to run against data

Running with Data Sources Other than Oracle • Connecting with Open Gateway: – Cursor

Getting and Setting the Commit Status • • What is commit status? SYSTEM. RECORD_STATUS:

Getting and Setting the Commit Status • SYSTEM. BLOCK_STATUS: – NEW (may contain records

Getting and Setting the Commit Status • SYSTEM. FORM_STATUS: – NEW – QUERY –

Getting and Setting the Commit Status • System variables versus built-ins for commit status

Getting and Setting the Commit Status • Example: If the third record of block

Array DML • • Performs array inserts, updates, and deletes Vastly reduces network traffic

Effect of Array DML on Transactional Triggers PRERepeated for each insert, update, delete PREFires

Implementing Array DML 1. Enable the Array Processing option. 2. Specify a DML Array

Summary • • • Post and commit phases • Characteristics and common uses of

Practice 20 Overview This practice covers the following topics: • Automatically populating order IDs

Slides: 31

Download presentation

Objectives After completing this lesson, you should be able to do the following: • Describe details of commit processing and commit triggers • Supplement transaction processing by using triggers • Allocate sequence numbers to records as they are applied to tables • Implement array DML 20 -2 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Transaction Processing FORM A Action Edit Transaction (Begin) Save Block#1 UPDATE Table 1 New Record Updated Record Block#2 Updated Record Deleted Record Transaction (End) 20 -3 INSERT INTO Table 1 DELETE FROM Table 2 UPDATE Table 2 Commit work; Copyright Ó Oracle Corporation, 1999. All rights reserved.

Transaction Processing Transaction processing includes two phases: • Post: – Writes record changes to base tables – Fires transactional triggers • Commit: Performs database commit Errors result in: • • 20 -4 Rollback of the database changes Error message Copyright Ó Oracle Corporation, 1999. All rights reserved.

The Commit Sequence of Events INSERT 1 UPDATE More records? Copy value from item Pre-Insert Pre-Update Check uniqueness On-Insert row Post-Insert Check uniqueness On-Update row Post-Update 2 More blocks? Post-Forms-Commit Stop 20 -6 Commit changes Post-Database-Commit Copyright Ó Oracle Corporation, 1999. All rights reserved.

Characteristics of Commit Triggers • Pre-Commit: Fires once if form changes are made or uncommitted changes are posted • • Pre- and Post-DML On-DML: Fires per record, replacing default DML on row Use DELETE_RECORD, INSERT_RECORD, UPDATE_RECORD built-ins 20 -7 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Characteristics of Commit Triggers • Post-Forms-Commit: Fires once even if no changes are made • Post-Database-Commit: Fires once even if no changes are made Note: A commit-trigger failure causes a rollback to the savepoint. 20 -8 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Commit Triggers Uses Pre-Commit Check user authorization; set up special locking Pre-Delete Journaling; implement foreign-key delete rule Pre-Insert Generate sequence numbers; journaling; automatically generated columns; check constraints Pre-Update Journaling; implement foreign-key update rule; auto-generated columns; check constraints 20 -9 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Commit Triggers Uses On-Insert/Update/Delete Replace default block DML statements Post-Forms-Commit Check complex multirow constraints Post-Database-Commit Test commit success; test uncommitted posts 20 -10 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Life of an Update Query 20 Column Rollback Locked Data 20 30 20 [Save] 30 20 Pre-Update 30 20 updated Row Updated 30 30 20 Post-Update 30 30 20 30 30 Update record in form Commit 20 -11 Item Copyright Ó Oracle Corporation, 1999. All rights reserved.

Delete Validation • • Pre-Delete trigger Final checks before row deletion DECLARE CURSOR C 1 IS SELECT ’anything’ FROM S_ORD WHERE customer_id = : S_CUSTOMER. id; BEGIN OPEN C 1; FETCH C 1 INTO : GLOBAL. dummy; IF C 1%FOUND THEN MESSAGE(’There are orders for this customer!’); RAISE form_trigger_failure; ELSE CLOSE C 1; END IF; END; 20 -12 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Assigning Sequence Numbers SELECT S_ORD_ID. nextval INTO : S_ORD. id FROM SYS. dual; Insert Pre-Insert ID 601 Value 1 60 2 60 602 20 -13 Database Sequence Copyright Ó Oracle Corporation, 1999. All rights reserved.

Keeping an Audit Trail • • Write changes to nonbase tables. Gather statistics on applied changes. Post-Insert example: : GLOBAL. insert_tot : = TO_CHAR(TO_NUMBER(: GLOBAL. insert_tot)+1); 20 -14 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Testing the Result of Trigger DML • • • SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT 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; 20 -15 Copyright Ó Oracle Corporation, 1999. All rights reserved.

DML Statements Issued During Commit Processing INSERT INTO base_table (base_column, . . . ) VALUES (: base_item, . . . ) UPDATE base_table SET base_column = : base_item, . . . WHERE ROWID = : ROWID DELETE FROM base_table WHERE ROWID = : ROWID 20 -16 Copyright Ó Oracle Corporation, 1999. All rights reserved.

DML Statements Issued During Commit Processing Rules: • • • DML statements may fire database triggers. • Locking statements are not issued. Form Builder uses and retrieves ROWID. The Update Changed Columns Only and Enforce Column Security properties affect UPDATE statements. 20 -17 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Overriding Default Transaction Additional transactional triggers: Trigger Do-the-Right-Thing Built-in On-Check-Unique CHECK_RECORD_UNIQUENESS On-Column-Security ENFORCE_COLUMN_SECURITY On-Commit COMMIT_FORM On-Rollback ISSUE_ROLLBACK On-Savepoint ISSUE_SAVEPOINT On-Sequence-Number GENERATE_SEQUENCE_NUMBER 20 -18 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Overriding Default Transactional triggers for logging on and off: Trigger Do-the-Right-Thing Built-in Pre-Logon - Pre-Logout - On-Logon LOGON On-Logout LOGOUT Post-Logon - Post-Logout - 20 -19 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Running with Data Sources Other than Oracle • Three ways to run against data sources other than Oracle – Oracle Open Gateways – Oracle Open Client Adapter for ODBC – Write appropriate transactional triggers 20 -20 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Running with Data Sources Other than Oracle • Connecting with Open Gateway: – Cursor and Savepoint mode form module properties – Key mode and Locking mode block properties • Using transactional triggers: – Call 3 GL programs – Database data block property 20 -21 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Getting and Setting the Commit Status • • What is commit status? SYSTEM. RECORD_STATUS: – NEW – INSERT (also caused by control items) – QUERY – CHANGED 20 -22 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Getting and Setting the Commit Status • SYSTEM. BLOCK_STATUS: – NEW (may contain records with status INSERT) – QUERY (also possible for control block) – CHANGED (block will be committed) 20 -23 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Getting and Setting the Commit Status • System variables versus built-ins for commit status • Built-ins for getting and setting commit status: – GET_BLOCK_PROPERTY – GET_RECORD_PROPERTY – SET_ RECORD _PROPERTY 20 -25 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Getting and Setting the Commit Status • Example: If the third record of block S_ORD is a changed database record, set the status back to QUERY. • Warnings: – Do not confuse commit status with validation status. – The commit status is updated during validation. 20 -26 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Array DML • • Performs array inserts, updates, and deletes Vastly reduces network traffic Empno Ename Job Hiredate 1234 Jones Clerk 01 -Jan-95 1235 Smith Clerk 01 -Jan-95 1236 Adams Clerk 01 -Jan-95 1237 20 -27 Clark Clerk 01 -Jan-95 Fewer round trips (exact number depends on array size) 2 inserts 2 updates 1 delete Copyright Ó Oracle Corporation, 1999. All rights reserved. Database

Effect of Array DML on Transactional Triggers PRERepeated for each insert, update, delete PREFires DML POST- POSTFires Array DML Size = 1 20 -28 Fires for each insert, update, delete Array DML Size > 1 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Implementing Array DML 1. Enable the Array Processing option. 2. Specify a DML Array Size of greater than 1. 3. Specify block primary keys. 20 -29 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Summary • • • Post and commit phases • Characteristics and common uses of commit triggers • • • Overriding default transaction processing Flow of commit processing DML statements issued during commit processing Getting and setting the commit status Implementing Array DML 20 -30 Copyright Ó Oracle Corporation, 1999. All rights reserved.

Practice 20 Overview This practice covers the following topics: • Automatically populating order IDs by using a sequence • Automatically populating item IDs by adding the current highest order ID • Customizing the commit messages in the CUSTOMERS form • Customizing the login screen in the CUSTOMERS form 20 -31 Copyright Ó Oracle Corporation, 1999. All rights reserved.