0

I want to store my old records in a table when something has changed. I tried to do it with this documentation. But i still get a error. The error is: relation serie doesn't exist.

The original table:

CREATE TABLE "Terra_nub_v1"."Serie"
(
  uuid uuid NOT NULL, -- t.b.v. pakbon
  serie_nr integer NOT NULL,
  aantal_slijpplaten integer,
  producent text,
  locatie text,
  klaar boolean,
  onderzocht boolean,
  gerapporteerd boolean,
  opmerking text,
  verwijzing_hoofdrapportage text,
  verwijzing_overige_rapportages text,
  onderzoeksgebied polygon,
  CONSTRAINT "Serie_pkey" PRIMARY KEY (serie_nr)
)

The archive table:

CREATE TABLE "Terra_nub_v1".serie_history
(
  uuid uuid NOT NULL,
  serie_nr integer NOT NULL,
  aantal_slijpplaten integer,
  producent text,
  locatie text,
  klaar boolean,
  onderzocht boolean,
  gerapporteerd boolean,
  opmerking text,
  verwijzing_hoofdrapportage text,
  verwijzing_overige_rapportages text,
  onderzoeksgebied polygon,
  datumtijd_wijziging timestamp without time zone,
  aangepast_door text,
  CONSTRAINT serie_history_pkey PRIMARY KEY (serie_nr)
)

I make the trigger function this way:

CREATE OR REPLACE FUNCTION "Terra_nub_v1".serie_history_trigger()
  RETURNS trigger AS $BODY$
  BEGIN
  IF (TG_OP ='UPDATE') THEN
    INSERT INTO Terra_nub_v1.serie_history SELECT OLD.*,NOW(), USER ; 
    RETURN OLD; 
  ELSEIF (TG_OP = 'DELETE') THEN
    INSERT INTO Terra_nub_v1.serie_history SELECT OLD.*,NOW(), USER ; 
    RETURN OLD; 
  END IF;
  RETURN NULL; 
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

Create Trigger serie_history_trigger
BEFORE UPDATE OR DELETE ON Terra_nub_v1.serie
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

I m using postgres

Greetings

waywer
  • 21
  • 11
  • 1
    `Terra_nub_v1.serie` needs to be `"Terra_nub_v1"."Serie"`. See the manual for details: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS but you should avoid quoted identifiers in the first place. Everything gets much if you never use double quotes in SQL –  Dec 07 '15 at 10:53
  • thank you, but i still get the error. I did the quotes but still get a error: ERROR: relation "Terra_nub_v1.serie" does not exist. – waywer Dec 07 '15 at 10:58
  • 1
    You need to use `"Terra_nub_v1"."Serie"` (quotes around each _part_ of the name). The error indicates you are using `"Terra_nub_v1.Serie"`. You also need to quote the schema name in your `insert` statements inside the function. Plus: the name of your function in the `create function` statement doesn't match the name of the function used in the `create trigger` statement (where you _also_ have to quote the schema name). –  Dec 07 '15 at 11:04
  • I posted here http://stackoverflow.com/a/33757751/2971632 my approach to log history. It's use an extension and triggers. May be having a look could give you some hints. – Luca Marletta Dec 07 '15 at 11:11

0 Answers0