APEX Training

Monday, 5 November 2007

Tabular Form on a table without a primary key (multiple primary keys)

One of the questions that frequently appear in the ApEx forum is "How to create a tabular form on a table without a primary key or on a table with multiple primary keys (more than two primary keys allowed by the wizard)?". The solution is simple and interesting - by using a view and two instead of triggers. The view is using the rowid column as a primary key. The two triggers - instead of update and instead of insert triggers - are managing the updates and inserts. See the example plus the corresponding code in my demo application here.

When you use the same view and trigger for a form on a view called from an interactive report (APEX 3.1). you may encounter problems with the automatic row processing while updating. After the automatic row processing APEX can't find the proper row due to implicit conversion of the primary_key (datatype ROWID). The work around is rather simple: use explicit row conversion. For example: rowidtochar(rowid) in the view definition and chartorowid(:new.primary_key) in the update trigger.