Re: Linking

On Tue, 2005-11-01 at 14:22 -0800, Bob Pawley wrote:
> I have created the following tables.

> CREATE TABLE process
> (
>   process_name varchar(60) NOT NULL,
>   fluid_id serial NOT NULL,
>   fluid varchar(30) NOT NULL,
>   ip_op_reactor varchar(3),
>   source varchar(30),
>   destination varchar(30),
>   CONSTRAINT process_pk PRIMARY KEY (fluid_id)
> );

> CREATE TABLE specification
> (
>   fluid_id int4 NOT NULL,
>   line_vessel_ident varchar(30),
>   CONSTRAINT specification_pk PRIMARY KEY (fluid_id),
>   CONSTRAINT specification_fluid_id_fk FOREIGN KEY (fluid_id)
> REFERENCES process (fluid_id)
> );

> On data entry I want the serial number generated in process.fluid_id
> to be transferred to column  specification.fluid_id.

You need a trigger function called by a trigger.

CREATE OR REPLACE FUNCTION process_trigger()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO specification (fluid_id) VALUES (NEW.fluid_id);
  END IF;
  RETURN NULL;
END;$$;

CREATE TRIGGER process_fluid_id AFTER INSERT ON process
   FOR EACH ROW EXECUTE PROCEDURE process_trigger();

> A simple insert/select command will transfer the serial number but
> entering a second row then employing an insert/select command violates
> the p_k unique rule.

I don't understand what you're saying at here.

You probably want to add ON UPDATE CASCADE to the foreign key on
specification.fluid_id, if updates are allowed to change the value of
process.fluid_id.  Similarly, if process records can be deleted, you
probably need to specify ON DELETE CASCADE.  Maybe too you want to add a
reverse foreign key on process.fluid_id; if so it would have to be
DEFERRABLE, so that the trigger could insert the records without causing
errors.


--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html