Malcolm Dew-Jones wrote:
> I have a task that is undoubtedly common, simple, but not trivial.
>
> I have a number of tables with minor variations, but basically what
> happens is this. Each row has a start date and an end date. The rows fall
> into sets, within a set of rows there can be at most one row that does not
> have an end date (i.e. "active"). An example is below, to clarify this.
>
> I want to accomplish two things.
>
> 1. I want to define a constraint that enforces the rule of one-or-zero
> "active" rows per set. How to do this is not obvious to me at all.
>
> 2. I want to write a trigger that forces older rows to be automatically
> end dated during various changes. This is "easy", lots of examples exist,
> but the code is not dead simple whereas I suspect some subtle but super
> simple technique exists, and I would like to find it.
>
> An example of two "sets". Each set is identified by the id. The PK is
> the id and the subid. set 1 has an active row and set 2 doesn't.
>
> id subid start_date end_date
>
> 1 1 1-jan-2000 31-dec-2000
> 1 2 1-jan-2001 31-dec-2001
> 1 3 1-jan-2002
> 2 1 1-jan-2000 31-dec-2000
> 2 2 1-jan-2001 31-dec-2001
> 2 3 1-jan-2002 31-dec-2002
>
> So, I'm looking for suggestions or pointers on how to define a constraint
> to enforce the end_date rule (0 or 1 row without an end date per set).
> Examples of variations on that theme would be just as welcome. I don't
> think I care whether the dates overlap, or any other complications, just
> the basic idea of 0-or-1 per set.
>
> If I insert a row with no end date, or update a row so it has no end date,
> then I would like the trigger to update the other rows in the set to have
> an end date. The "text book" way to do this is to save the ids of all the
> modified rows during the row level trigger, and then apply logic during
> the table level after trigger. Like I said though, the data looks simple
> enough that I suspect some dead simple code would work if applied with due
> intelliegence to its design.
>
> For feedback, thanks. Malcolm.
Tom Kyte has examples of doing this at http://asktom.oracle.com using
a unique function based index.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Nov 22 2007 - 02:24:59 CST
