Thread: Constraint doesn't see a currently insertet record

Hi,

yes that's my problem.
I've got a table and I put lots of contraints on it so that data stays
consistent. One constraint calls a fcn to do some kind of count() over that
table but it omits the 'to be inserted record'. What to do?

concrete problem:
Suppose a table i | tel | status | .....| \d+ | 1-7    | ...
status can be active, deleted or some more values
There may be not two rows where tel is equal and status is not deleted.
Other: As long as status=deleted there may be duplicate tel columns.

How would I make that sure?


My try was to create a check constraint like
("status" = 7) OR (("status" <> 7) AND (num_of_equal_tel_status_not_7("tel")
<= 1))

where num_of_equal_tel_status_not_7 is:
SELECT count(*) FROM "this table" WHERE "tel"=$1 AND "status"<>7

Note: status=7 means deleted


This works well until two recoreds are inserted which are status<>7.
The second gets inserted because the new record is not yet visible.


Another Idea was to make a trigger. But BTW how do I access a trigger
parameter if my trigger function must not have any parameter??????????

Ideas?



Tahks for any

On Fri, Feb 11, 2005 at 07:10:50PM +0100, KÖPFERL Robert wrote:

> Another Idea was to make a trigger. But BTW how do I access a trigger
> parameter if my trigger function must not have any parameter??????????

PL/pgSQL triggers can access arguments via TG_ARGV.

http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Thanks.

I managed it via a trigger.



> -----Original Message-----
> From: Michael Fuhr [mailto:mike@fuhr.org]
> Sent: Sonntag, 13. Februar 2005 02:57
> To: KÖPFERL Robert
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Constraint doesn't see a currently insertet record
>
>
> On Fri, Feb 11, 2005 at 07:10:50PM +0100, KÖPFERL Robert wrote:
>
> > Another Idea was to make a trigger. But BTW how do I access
> a trigger
> > parameter if my trigger function must not have any
> parameter??????????
>
> PL/pgSQL triggers can access arguments via TG_ARGV.
>
> http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>