Re: Supporting multiple oracle versions in a trigger

"HansF" <News.Hans_at_telus.net> wrote in message news:pan.2005.10.19.21.37.34.954878_at_telus.net...

> Pretty please - explain WHY you do this. While there are valid reasons,
> they are few and far between.

I will try to explain and am interested in your feedback/suggestions or enhancment ideas to clean things up from a dba side.

Basic Overview:

My application is a data driven generic data collection tool that allows the client to define 'data collections' which are made up of one or more 'data fields'. I have a collection table, a field definition table, and a collection_field table.

These collection/field combos are basically pseudo tables/columns to the user but I store / manage all the data in five core tables (numbers,strings,dates,attachments, clobs) and use data header tables to keep track of everything and link the data back to the appropriate collection/field. I collect each piece of data as a single row in the core tables.

The easiest example is collecting data from a test - Each field is a question and answer. The field.txt column is the question and the field.definition columns define the type of data accepted. Depending on the data type the actual data is stored in the appropriate core data table.

I then offer two methods of updating the data for the user:

1. I use a generic view and a insteadof trigger to present the data (one field per row) and then on update pass the data based on type to an appropriate update function. This allows the client to quickly modify or add to the data collection. Fields can be dropped, added, hidden, re-formatted, etc without need to recompile anything.
2. I have one transpose view per collection that uses lookup functions to transpose the data from the cores tables into a single row. This also has an instead of trigger that updates the data. This method allows the use of custom data entry screens but restricts how much change the client can do to the collection.

Reason for Dynamic SQL:

The reason for dropping/creating tables is that I provide real indexed tables for each data collection broken out by client. These tables are used for advanced searching, separating different clients data, as well as providing simple data dumps for clients to query from other tools. After each update on the data I refresh the client tables with a simple delete/insert from the transpose view. The transpose view works very quickly with primary keys but isn't great for generic searches. Ideally I would have used snapshots but I couldn't get quick refreshes due to the complicated sql in the view.

Whenever the client updates the collection/field definition, such that the base table is out of date, I drop the table, recreate it, and repopulate it. The frequency once the client is setup and collecting data is quite rare and is done as part of an administrative process. I haven't done too much collision testing where data updates are going on while the client tables are being re-created.

Any thoughts are greatly appreciated.

thx

Jack Received on Thu Oct 20 2005 - 15:20:37 CDT