How to modify Trigger to update a single attribute in PostgreSQL

Question

Here is my sample table.

CREATE TABLE employee_test(
idTst SERIAL PRIMARY KEY, 
monthDownload VARCHAR(6),
changeDate DATE);

I am trying to create a function and trigger that would update changeDate attribute with a current date when monthDownload attribute is updated. The function I have it works with one problem. It updates all records instead of the one that was updated.

 CREATE OR REPLACE FUNCTION downloadMonthChange() 
 RETURNS TRIGGER AS 
 $$
 BEGIN
 IF NEW.monthDownload <> OLD.monthDownload THEN
 UPDATE employee_test
 SET changeDate = current_date
 where OLD.idTst = NEW.idTst;
 END IF;
 RETURN NEW;
 END;
 $$ 
 Language plpgsql;

Trigger

Create TRIGGER dataTest
AFTER UPDATE
ON employee_test
FOR EACH ROW
EXECUTE PROCEDURE downloadMonthChange();

When I execute the following Update statement:

UPDATE  employee_test SET  monthDownload = 'oct12'
WHERE idTst = 1;

All changeDate rows get update with a current date. Is there a way to have only a row with changed record to have a current date updated.

Jasen · Accepted Answer · 2017-01-09 04:34:44Z

3

If you use a before trigger you can write directly to NEW

 CREATE OR REPLACE FUNCTION downloadMonthChange() 
 RETURNS TRIGGER AS 
 $$
 BEGIN
 IF NEW.monthDownload <> OLD.monthDownload THEN
   NEW.changeDate = current_date;
 END IF;
 RETURN NEW;
 END;
 $$ 
 Language plpgsql;

the other option when you must use an after trigger is to include the primary key in the where clause. It appears that you were trying to do this, but you had a spurious OLD in the query. beause of that the where clause was only looking at the record responsible for the trigger call, and not limiting which records were to be updated.

 IF NEW.monthDownload <> OLD.monthDownload THEN
 UPDATE employee_test
 SET changeDate = current_date
 where idTst = NEW.idTst;

answered Jan 9, 2017 at 4:34

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

How to modify Trigger to update a single attribute in 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