473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Only 1 of 3 columns per row can ever be populated!

Thanks to denormalised developers, I have a table where only one
column of three can ever be populated per row. I feel
a) BEFORE INSERT/UPDATE triggers would have an unacceptable
performance impact (this table will be accessed heavily).
b) creating three views would be clumsy.
c) restricting inserts/updates using the application is far too much
to ask.

Does anyone have any bright ideas?

Thanks
Bruce
Nov 12 '05 #1
4 1450
It would be easier to give advice if saw the DDL and some sample rows
for the table. Could you define all three columns with default values
and somehow define a constraint to enforce the rules you want?

Bruce Pullen wrote:
Thanks to denormalised developers, I have a table where only one
column of three can ever be populated per row. I feel
a) BEFORE INSERT/UPDATE triggers would have an unacceptable
performance impact (this table will be accessed heavily).
b) creating three views would be clumsy.
c) restricting inserts/updates using the application is far too much
to ask.

Does anyone have any bright ideas?

Thanks
Bruce


Nov 12 '05 #2
Bruce,

Is this dynamic SQL? As long as you exploit dynamic statement cache and or
use static SQL I don't see how a well written before trigger with a SIGNAL
statement can have a significant negative performance impact. The runtime
impact should be the evaluation time for the condition.
Keep in mind that DB2 for LUW uses inline SQL PL for triggers. Can't get any
faster than that.

Cheers
Serge
Nov 12 '05 #3
Bruce Pullen <br**********@hotmail.com> wrote:
Thanks to denormalised developers, I have a table where only one
column of three can ever be populated per row. I feel
a) BEFORE INSERT/UPDATE triggers would have an unacceptable
performance impact (this table will be accessed heavily).
b) creating three views would be clumsy.
c) restricting inserts/updates using the application is far too much
to ask.


What's the behavior that you want to have if 2 (or 3) of these columns are
to be populated? If you want to return an error, then a simple check
constraint on the table could be sufficient:

CHECK ( col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL OR
col1 IS NULL AND col2 IS NOT NULL AND col3 IS NULL OR
col1 IS NULL AND col2 IS NULL AND col3 IS NOT NULL )

If you want to handle the situation in the database system, then you would
need some more logic and triggers could be the way to go.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4
After proposing a check constraint and a trigger, we're going for the
check constraint (I thought it wouldn't be possible to enforce this
logic using check constraints so thank you).

Thank you.
Bruce
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul | last post by:
Hi I have a table that currently has 466 columns and about 700,000 records. Adding a new DEFAULT column to this table takes a long time. It it a lot faster to recreate the table with the new...
1
by: Peter | last post by:
Hello, Thanks for reviewing my question. My SQL table has a few string columns and a image column. After inserting a new row into the dataset with the columns populated, I tried calling the...
0
by: Jordo | last post by:
Help! I have a datagrid that is being populated after a button click to fill based on parameters set by the user at run-time. I am creating the columns programmatically from a datatable and...
1
by: coleenholley | last post by:
I'm getting frustrated with trying to format a datagrid that is entirely populated dynamically using a class module written in VB .Net (No C# used!) We connect to a DB2 database using an RPC that is...
1
by: Mac via DotNetMonster.com | last post by:
Hi all, I have a datagrid on a form which is not populated until the user enters selection criteria and clicks a button. Rather than have a blank datagrid when the form is displayed, I would...
4
by: Greg | last post by:
I have a VB.NET Windows application with bound DataGrid control with it’s DataSource property set to a DataSet object. The DataSet object is created from an XML Schema. I would like to define...
0
by: schoultzy | last post by:
Hello Everyone, I have been trying to figure this one out for two days now. I have created a DataGridView which is populated by an ObjectDataSource. My problem occurs when I attempt to use the...
2
by: Richard Maher | last post by:
Hi, Recently on the web I came across documentation discussing Data Source Objects (DSO) in relation to browser and html functionality (in particular the ability to declare a Java applet as a...
6
by: napatel04 | last post by:
Hi everyone, I would like to know if there is a quick query someone can help me write for the following scenario. I think I can do this with VBA but since this is suppose to be a temp. solution,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.