Re: error when creating rule

On Fri, 7 Nov 2003, Barbara Lindsey wrote:

> I see what you mean - yes - corrections to post below:
>
> > On Fri, 7 Nov 2003, Barbara Lindsey wrote:
> >
> >> CREATE SEQUENCE "prod_id_seq" cache 1;
> >> CREATE TABLE "prod_data" (
> >>         "prod_id" integer DEFAULT nextval('prod_id_seq') PRIMARY KEY,
> >> "client_id" integer NOT NULL,
> >>         "cat_id" integer NOT NULL,
> >>         "status_id" integer NOT NULL,
> >>         "modified" timestamp with time zone DEFAULT
> >> current_timestamp, "modified_by" varchar(50) DEFAULT
> >> current_user,
> >>         CONSTRAINT prod_clnt_fk FOREIGN KEY (client_id)
> >>            REFERENCES ref_clients(client_id)
> >>            ON DELETE CASCADE ON UPDATE CASCADE,
> >>         CONSTRAINT prod_cat_fk FOREIGN KEY (cat_id)
> >>            REFERENCES ref_category(cat_id)
> >>            ON DELETE CASCADE ON UPDATE CASCADE,
> >>         CONSTRAINT prod_stat_fk FOREIGN KEY (status_id)
> >>            REFERENCES ref_status(status_id)
> >>            ON DELETE CASCADE ON UPDATE CASCADE
> >> );
> >>
> >> TABLE  prod_data_bak looks just like prod_data but with all constraints
> >> removed.
> >>
> >>
> >> Here is where the problem begins. When I try to create this rule:
> >>
> >> CREATE RULE log_prod_upd AS ON UPDATE TO prod_data
> >>      where NEW.prod_id = OLD.prod_id
> >>      DO INSERT INTO prod_data_bak VALUES (
> >>           OLD.prod_id,OLD.client_id,
> >>           OLD.cat_id, OLD.status_id,
> >>           OLD.modified,OLD.modified_by
> >>      );
> >
> > Was this intended to go to prod_data_bak as opposed to job_data_bak?

What version are you running?

I had to drop the extra references constraints to other tables that
weren't given, but had no problems with the creation on the rule once I
did so, given creating a prod_data_bak that didn't have any of the
references constraints (or the primary key) and it seemed to work for me
(using my 7.3.4 system)