Message-ID: <43677F75.9030409@brandywine.com>
Date: Tue, 01 Nov 2005 09:45:09 -0500
From: Daryl Richter <daryl@brandywine.com>
Reply-To: daryl@brandywine.com
Organization: Brandywine Asset Management
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: lucas@presserv.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: Re: Referencing
References: <20051028105908.onaegu44ynnog8os@www.presserv.org>
	<43624661.7000404@brandywine.com>
	<20051028130720.jxoth8izb468k44s@www.presserv.org>
	<4366471F.1020509@brandywine.com>
	<20051031165827.50z4qeezsnms8kwk@www.presserv.org>
In-Reply-To: <20051031165827.50z4qeezsnms8kwk@www.presserv.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

lucas@presserv.org wrote:
> Quoting Daryl Richter <daryl@brandywine.com>:
> 
>> lucas@presserv.org wrote:
>> > Quoting Daryl Richter <daryl@brandywine.com>:
>> >> It's hard to say without knowing more precisely what you are trying to
>> >> model, but I think this push you in the right direction:
>> >>
>> > Okay, but references between (output/input) and ACTIVITY tables is 1 
>> to N.
>> > OUTPUT/INPUT - 1
>> > to
>> > ACTIVITY - N.
>> > And not N to 1 how the example.
>> > Then the reference field need to be on "ACTIVITY (send/buy)" table.
>> >
>>
>> Ahh, ok.  In that case I reverse it like so:
>>
>> -- This table hold everything in common for inputs/outputs
>> create table transfer(
>> id            serial   primary key
>> );
> 
> 
> Yes, I think it was what I wanted.
> And how I check if a register in "Transfer" table is only referenciable 
> by ONE
> table (OR "output" OR "input")?? Would I create a Trigger like:
> CREATE or REPLACE FUNCTION TG_output_check() RETURNS TRIGGER AS
> $$
>  BEGIN
>   IF exists (select 1 from input where transfer_id=NEW.transfer_id) THEN
>    Raise Exception 'This activity (transfer) is alread setted to INPUT';
>   END IF;
>   RETURN NEW;
>  END;
> $$ language 'plpgsql'; CREATE TRIGGER TG_output_check BEFORE INSERT or 
> UPDATE
> on OUTPUT EXECUTE PROCEDURE TG_output_check();
> CREATE or REP...--- and the some function to INPUT ---
> 
> Or is there another way to check it?
> 
> Thank you again.
> 

Exactly, except for the small change that your trigger declaration needs 
"for each row" as shown below:

CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE on output
for each row EXECUTE PROCEDURE TG_output_check();

-- 
Daryl