Trigger on PostgreSQL

Question

I need some help. I´m new to SQL and everything I want is a trigger to update a column (est) when I use INSERT with sex = 'F' in the Peop table. Is it possible? My DBMS is PostgreSQL.

I´ve tried this code but isn´t right. I´m getting this hint: If you want to discard the results of a SELECT, use PERFORM instead.

CREATE FUNCTION tEst() RETURNS TRIGGER AS $$
   BEGIN
      SELECT * FROM Peop;
      IF Peop.est = 'A' AND Peop.sex = 'F' THEN
         UPDATE Peop SET est = 'B';
      END IF;
      RETURN NULL;
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER myTrigger AFTER INSERT ON Peop
FOR EACH ROW
EXECUTE PROCEDURE tEst();

INSERT INTO Peop (name, sex) VALUES ('Mary', 'F');

By default, est column have 'A' value. Thanks in advance!

Pavel Stehule · Accepted Answer · 2015-01-31 06:31:16Z

2

It is simple task, but you should to use a BEFORE INSERT OR UPDATE row trigger. There is possibility

get information about data in related (inserted, updated, deleted) rows.
modify data, that will be stored in table.

Your example:

CREATE FUNCTION tEst() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.est = 'A' AND NEW.sex = 'F' THEN
    NEW.est = 'B';
  END IF;
  RETURN NEW;
END;

CREATE TRIGGER tEst_trg BEFORE INSERT OR UPDATE ON peop
FOR EACH ROW EXECUTE PROCEDURE tEst();

You can see more examples in related plpgsql documentation

edited Jan 31, 2015 at 6:31

answered Jan 30, 2015 at 21:15

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger on PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related