CODEWITHSUNDEEP
sqlpostgresqlstored-procedurestriggers
eightShirt
eightShirt

Reputation: 1447

Trigger on PostgreSQL

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!

Upvotes: 0

Views: 54

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45760

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

Upvotes: 2

Related Questions