在表A更新后，触发器更新表A中的特定列。

I am using postgresql and I want to update a column by summing up other column in the table.

我正在使用postgresql，我想通过总结表中的其他列来更新一个列。

EX: table name is A. When col1 or col2 or any column value in table A is updated then the value of col6 should also be updated as col6=(col1+col2+col3)

例:表名是A。当col1或col2或表A中的任何列值被更新时，col6的值也应该更新为col6=(col1+col2+col3)

for this i have written a update trigger as below.

为此，我编写了如下所示的更新触发器。

CREATE FUNCTION update_total2() RETURNS TRIGGER AS $_$
BEGIN
    UPDATE hr_contract SET "x_TOTAL"=(NEW.x_othr_allow+NEW.x_med_allw+NEW."x_LTA"+NEW.wage+NEW.supplementary_allowance) WHERE id = OLD.id;
    RETURN OLD;
END $_$ LANGUAGE 'plpgsql';


CREATE TRIGGER hr_contract_after_update
    AFTER update ON hr_contract

    FOR EACH ROW
    EXECUTE PROCEDURE update_total2();

It is giving error as below..

它给出的错误如下。

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."res_users" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
SQL statement "UPDATE hr_contract SET "x_TOTAL"=(NEW.x_othr_allow+OLD.x_med_allw+OLD."x_LTA"+OLD.wage+OLD.supplementary_allowance) WHERE id = OLD.id"
PL/pgSQL function update_total_result() line 3 at SQL statement
SQL statement "UPDATE hr_contract SET "x_TOTAL"=(NEW.x_othr_allow+OLD.x_med_allw+OLD."x_LTA"+OLD.wage+OLD.supplementary_allowance) WHERE id = OLD.id"

Any help is really very useful...

任何帮助都是非常有用的……

2 个解决方案

#1

Try to check if any thing has changed on that row, and set the value of field x_TOTAL if any:

尝试检查该行上是否有任何更改，并设置字段x_TOTAL的值(如果有):

CREATE FUNCTION update_total2() RETURNS TRIGGER AS $_$
  BEGIN
--Check if any of columns has been updated:
  if tg_op='UPDATE' and  old <> new then 
    NEW."x_TOTAL"= NEW.x_othr_allow+NEW.x_med_allw+NEW."x_LTA"+NEW.wage+NEW.supplementary_allowance);
  end if;

RETURN NEW;
END $_$ LANGUAGE 'plpgsql';

--Associate triger to table on BEFORE update events
CREATE TRIGGER hr_contract_after_update
    before update ON hr_contract

    FOR EACH ROW
    EXECUTE PROCEDURE update_total2();

Trigger documentation

触发文档

#2

I'm not familiar with postgresql syntax but the problem is that you are doing a recursive update. Your function update_total2 must not use UPDATE to the same table being updated on the trigger.

我不熟悉postgresql语法，但问题是您正在执行递归更新。您的函数update_total2不能使用对触发器上正在更新的同一表的更新。

Please check documentation for details, all you have to do in the trigger itself is something like:

请查看文档了解详细信息，您在触发器中所要做的就是:

FOR EACH ROW
    NEW.x_TOTAL = NEW.x_othr_allow + NEW.x_med_allw  + NEW."x_LTA" + NEW.wage + NEW.supplementary_allowance

#1