Re: unique value - trigger?

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

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

Dima

>
>