DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.de;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=f271Bt5/xzt07ymG733UcFqJ63poX7apebFMg4n4b0cMvyXO8Mj2Xzq4lBFJwfGkf9xNA64ra52FvtRcpEiJ21037UVxWGxxzwDDIll1xiCr/IPrqlzTof2VOq1cjia5FhGOEsmtykG57/IeKMXqwSI2wQnO0xo03wcUg2QXUOg=
	;
Message-ID: <20051228152823.2471.qmail@web26001.mail.ukl.yahoo.com>
Date: Wed, 28 Dec 2005 16:28:23 +0100 (CET)
From: J Crypter <jcrypter@yahoo.de>
Subject: instead of trigger in pg
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi,

I would like to implement a 1:n relation between two
tables. 
An auto-generated number should be used as primary key
which connects both tables. 
Example:

table 1:
name | number (prim_key)

table 2:
country | number_table1 (foreign key)

View:
number_table1 | name | country 

I would like to insert into both tables in one step.
"Insert into view (name,
country)values('name','country');"

Oracle supports an instead-of-trigger but Postgres
doesn't do this.

CREATE or replace FUNCTION insertInto() RETURNS
trigger AS '
    BEGIN
    	insert into table1(name)values(NEW.name);
        insert into
table2(number,country)values(NEW.number,NEW.country);
	
        RETURN NEW;
    END;
' LANGUAGE plpgsql;

CREATE TRIGGER insert_TR BEFORE INSERT OR UPDATE ON
view
    FOR EACH ROW EXECUTE PROCEDURE insertInto();


I would like to implement something like this but I
don't know how to do.

Thank you for you help!

Jakob


___________________________________________________________ 
Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de