CODEWITHSUNDEEP
postgresqltriggers
Truut
Truut

Reputation: 83

Record old is not assigned on Update Statement

I created a Trigger on Update which should insert in my table preishist.

//preishist
CREATE TABLE preishist (
  updatenr varchar(4),
  preis numeric(8,2),
  pnr varchar(4),
  gueltigab date,
  PRIMARY KEY(updatenr));

//produkt
CREATE TABLE produkt(
pnr varchar(4),
name varchar(4),
preis numeric(8,2),
gultigab date,
PRIMARY KEY(pnr));

//Trigger
CREATE TRIGGER trigger AFTER UPDATE ON produkt FOR EACH STATEMENT EXECUTE 
PROCEDURE triggerfunc();


//Triggerfunc
CREATE FUNCTION triggerfunc RETURNS TRIGGER AS '
begin 
   IF TG_OP = 'UPDATE' THEN 
      IF OLD.preis <> NEW.preis AND OLD.gueltigab < NEW.gueltigab THEN
         INSERT INTO preishist (preis,pnr,gultigab) VALUES (NEW.preis, NEW.pnr,NEW.gueltigab);
      end IF;
   end IF;                                                                                              
end;

When I try to update produkt I get the error message

Record old is not assigned yet.

I'm happy for all help.

Upvotes: 1

Views: 334

Answers (1)

user330315
user330315

Reputation:

OLD and NEW are only available in row level trigger. You defined your trigger as a statement level trigger.

To be able to use OLD and NEW use:

CREATE TRIGGER trigger 
  AFTER UPDATE ON produkt 
  FOR EACH ROW --<< here
  EXECUTE PROCEDURE triggerfunc();

Upvotes: 3

Related Questions