Postgresql trigger run non stop?

Question

I try to work with trigger here. I have a relation like this :

salary(salaryid, userid, netsalary, reward, totalsalary)

So I want to update totalsalary everytime I insert and update (netsalary or reward), it will recount : totalsalary = netsalary + reward.

To do that, I made a function and a trigger :

CREATE FUNCTION reCount()
  RETURNS TRIGGER AS $function$
BEGIN 
  UPDATE salary SET totalsalary = netsalary + reward;
  RETURN NEW;
END;

CREATE TRIGGER updateTotalsalary
  AFTER INSERT OR UPDATE
  ON salary
  FOR EACH ROW
  EXECUTE PROCEDURE reCount();

Finally, I try to test by a query insert like this :

INSERT INTO salary(salaryid,userid,netsalary,reward,totalsalary) 
    VALUES (1234,123, 30,2,30);

but it run for a long time and it seem never stop. So when a try to stop it with, I got many rows of :

SQL statement "UPDATE salary SET totalsalary = netsalary + reward"
PL/pgSQL function "reCount()" line 3 at SQL statement

So what is the problem. Hope you guys can give me some suggestion?

I am just wondering if the trigger could trigger itself by the update? — user2672165, May 10 '14 at 09:30
Thanks user2672165, I see the problem. So after I make the query update in the function, it will do that again and again... Thank you so much! — allicius, May 10 '14 at 09:37
possible duplicate of [Prevent recursive trigger in PostgreSQL](http://stackoverflow.com/questions/708562/prevent-recursive-trigger-in-postgresql) — Daniel Vérité, May 10 '14 at 13:02
You are updating ***all*** rows in the table each time you update a single row. Are you sure you want to do that? I think you actually want `new.totalsalry = new.netsalary + new.reward` instead of an `update` statement in the trigger function. — , May 10 '14 at 13:15

score 0 · Accepted Answer · answered May 10 '14 at 09:39

0

Try:

CREATE TRIGGER updateTotalsalary 
AFTER INSERT OR UPDATE 
ON salary 
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE reCount();

answered May 10 '14 at 09:39

user2672165

2,986
19
27

I have found another solution, I don't use UPDATE anymore but **NEW.totalsalary = NEW.netsalary + NEW.reward** And it work fine! – allicius May 10 '14 at 09:56
Yes that is much better! – user2672165 May 10 '14 at 09:57
What if `salary` is updated from another trigger? This solution doesn't work. – Daniel Vérité May 10 '14 at 13:13
"What if this and that" was not part of the question, but yes to this problem there are better solutions. I at least helped in finding the cause (see initial comment). – user2672165 May 10 '14 at 13:23

score 0 · Answer 2 · answered May 10 '14 at 10:13

this might be better than the pg_trigger_depth() = 0 hack:

CREATE TRIGGER updateTotalsalary 
AFTER INSERT OR UPDATE OF netsalary, reward
ON salary 
FOR EACH ROW
EXECUTE PROCEDURE reCount();

For UPDATE events, it is possible to specify a list of columns using this syntax:

UPDATE OF column_name1 [, column_name2 ... ]

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

though personally i'd probably go for a table without the totalsalary column and a view with it (CREATE TABLE salary_ ..., CREATE VIEW salary AS SELECT ..., (salary + reward) AS totalsalary FROM salary_).

Postgresql trigger run non stop?

2 Answers2