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.





3 comments:

Stessy said...
This comment has been removed by the author.
Jornica said...

Dennes,

Nice example!

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.

With kind regards,

Jornica

Unknown said...

Would your approach towards building a tabular form change with new features available in Apex 4.0?

Thanks