Re: unique value - trigger?

On Thursday 17 Jul 2003 3:34 pm, Dmitry Tkach wrote:
> Gary Stainburn wrote:
> >Hi folks,
> >
> >I'm back with my lnumbers table again.
> >
> >nymr=# \d lnumbers
> > Table "lnumbers"
> > Column | Type | Modifiers
> >-----------+-----------------------+-----------
> > lnid | integer | not null
> > lnumber | character varying(10) | not null
> > lncurrent | boolean |
> >Primary key: lnumbers_pkey
> >Triggers: RI_ConstraintTrigger_7575462
> >
> >While each loco can have a number of different numbers, only one can be
> >current at any one time.
> >
> >I want to make it so that if I set lncurrent to true for one row, any
> > existing true rows are set to false.
> >
> >I'm guessing that I need to create a trigger to be actioned after an
> > insert or update which would update set lncurrent=false where lnid not =
> > <current lnid>
>
> Why "not"? I thought, you wanted just the opposite - update the ones
> that *do* have the same lnid?
> I'd also recommend you to add ' and lncurrent' to the query - otherwise
> every insert would be updating *every* row with the same lnid (it
> doesn't check if the new row is actually the same as the old one) before
> updating, and that may be expensive.
> You may also want to create a pratial index on lnumbers (lnid) where
> lncurrent to speed up your trigger

All good and valid points.

>
> >but I can't seem to sus it put.
>
> What is the problem?

The problem is I don't know how to convert the following pseudo code to valid
SQL:

create trigger unique_current on insert/update to lnumbers
if new.lncurrent = true
update lnumbers set all other records for this loco to false

>
> Dima
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000