trigger to update particular column in table A after update on table A

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

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)

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...

Answers


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

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


Need Your Help

CSS selector engine clarification?

css css-selectors

I've always believed (although I now doubt the validity of these beliefs) that:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.