postgreSQL There is a table

CREATE TABLE payments( pmId SERIAL PRIMARY KEY, pmNumber CHAR(64) UNIQUE NOT NULL, pmDate TIMESTAMP DEFAULT now(), pmSum NUMERIC(9,2) NOT NULL CHECK(pmSum >= 0), pmRest NUMERIC(9,2) NOT NULL CHECK(pmRest >= 0), CONSTRAINT valid_pm_balance CHECK(pmSum-pmRest >= 0));

Could you tell me as under request:

 INSERT INTO payments (pmNumber,pmSum) VALUES (7,968)

Field

pmRest

Filled just like

pmSum

  • make a trigger to insert into a table that value - Mike
  • Change request INSERT INTO payments (pmNumber,pmSum,pmRest) VALUES (7,968,968) - 0xdb
  • The request must be of this type. - Vetos
  • Why? You can not change the request? Something is wrong with the design. - 0xdb
  • The user does not need to know about the pmRest field. This field is supposed to change automatically depending on the requests. - Vetos

1 answer 1

 CREATE FUNCTION set_payment_rest_like_sum() RETURNS trigger AS ' BEGIN NEW.pmRest=NEW.pmSum; return NEW; END; ' LANGUAGE plpgsql; -- Создание триггера CREATE TRIGGER set_rest BEFORE INSERT ON payments FOR EACH ROW EXECUTE PROCEDURE set_payment_rest_like_sum();