Postgresql Update Trigger

PostgreSQL Update trigger

You are triggering an endless loop. Simplify the trigger function:

CREATE OR REPLACE FUNCTION set_angle()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW."rotationAngle" := degrees(
                             ST_Azimuth(
                                ST_StartPoint(NEW.the_geom)
                              , ST_EndPoint(NEW.the_geom)
                             )
                          ) - 90;
   RETURN NEW;
END
$func$;

Assign to NEW directly. No WHERE in this case.
You must double-quote illegal column names. Better not to use such names to begin with.

Recent related answer.
Code for insert & upgrade is the same. I folded into one code path.

Use a BEFORE trigger. This way you can edit columns of the triggering row directly before they are saved:

CREATE TRIGGER set_angle
BEFORE INSERT OR UPDATE ON annotations
FOR EACH ROW EXECUTE PROCEDURE set_angle();

However

If you are just trying to persist a functionally dependent value in the table (and there are no other considerations): Don't. Use a view or a generated column instead:

Store common query as column?

Then you don't need any of this.

Postgresql using Trigger to update column values after inserting values

It is terribly inefficient to update the row after you inserted it. Better is to use a BEFORE trigger that can modify the new row before it is inserted:

CREATE OR REPLACE FUNCTION cal() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN 
   NEW.amount_hkd := NEW.amounts * NEW.currency_conversion_rate;
   RETURN NEW; 
END;$$;

CREATE TRIGGER update_amount 
   BEFORE INSERT ON tableA FOR EACH ROW 
   EXECUTE PROCEDURE cal();

Trigger function and trigger to change one column after an update

You can try a function like this:

CREATE OR REPLACE FUNCTION test_set_false()
  RETURNS "pg_catalog"."trigger" AS $BODY$
    DECLARE
BEGIN

if new.col2 = true then 
update test_table set col2 = false where col2 = true and id != new.id;
end if;

return new;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

and call it in trigger (before update)

CREATE  TRIGGER "test_set_false_update_trigger" BEFORE update ON test_table
                FOR EACH ROW
                EXECUTE PROCEDURE test_set_false();

Trigger Function Doesn't Update the Specified Table

(a) Should you insert a new row in table payment_detail when inserting a new row in table payment ? If so, you have to create two separated trigger functions insert_payment_detail() and update_payment_detail().

(b) In the trigger function you have to refer to the fields of the current row which is inserted/updated in table payment by using the NEW variable, see the manual.

As a result, the trigger functions may be :

CREATE OR REPLACE FUNCTION insert_payment_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   INSER INTO payment_detail (customer_id, first_name, last_name, payment_id, amount )
      SELECT NEW.customer_id, customer.first_name, customer.last_name, NEW.payment_id, NEW.amount
      FROM customer
      WHERE customer.customer_id = NEW.customer_id;
      RETURN NULL;
END;
$$ ;

CREATE TRIGGER insert_payment_detail
   AFTER INSERT ON payment
   FOR EACH ROW EXECUTE PROCEDURE insert_payment_detail();

CREATE OR REPLACE FUNCTION update_payment_detail()
   RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
   UPDATE payment_detail
      SET
      customer_id = NEW.customer_id,
      first_name = customer.first_name,
      last_name = customer.last_name,
      payment_id = NEW.payment_id,
      amount = NEW.amount
      FROM customer
      WHERE customer_id = NEW.customer_id;
      RETURN NULL;
END;
$$ ;

CREATE TRIGGER update_payment_detail
   AFTER UPDATE ON payment
   FOR EACH ROW EXECUTE PROCEDURE update_payment_detail();

(c) Instead of duplicating the data from tables customer and payment into table payment_detail, you could create a view payment_detail, see the manual :

CREATE OR REPLACE VIEW payment_detail (customer_id, first_name, last_name, payment_id, amount ) AS
SELECT customer.customer_id, customer.first_name, customer.last_name, payment.payment_id, payment.amount)
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id ;

How to trigger AFTER UPDATE in Postgres

To fire your trigger for all data change events, code the event as INSERT OR UPDATE OR DELETE:

CREATE TRIGGER tr_new_rentals
AFTER INSERT OR UPDATE OR DELETE ON public.rental
FOR EACH ROW
    EXECUTE FUNCTION public.fn_rental_trigger()

Since the top 10 data depends only on data in tables, and not the event (insert, update or delete) that caused the data to change, the trigger may be safely defined as one trigger for all data change events.

PosgreSQL trigger function update after insert

If you want to change the row that was just inserted, don't use UPDATE assign the value to the field or the new record.

To access the column values, use the new record as well.

Something like this:

CREATE OR REPLACE FUNCTION czynsz_clear() 
  RETURNS TRIGGER 
AS $body$ 
BEGIN
  if new.administrative_fees IS NOT NULL 
     AND new.administrative_fees_m2 IS NULL 
     AND new.area IS NOT NULL
     AND new.type_id IN (6,1)
     AND new.administrative_fees > 1 
     AND new.area > 1
  THEN    
    new.administrative_fees_m2 := TRUNC((administrative_fees/ AREA)::INTEGER,2);
  END IF;

  RETURN NEW; -- this needs to be outside of the IF
END;
$body$ LANGUAGE plpgsql;

This assumes the trigger is defined as a row level trigger, e.g.

You also need to make it a BEFORE trigger

create trigger ..
  BEFORE INSERT OR UPDATE on ....
  FOR EACH ROW execute procedure czynsz_clear();

The error you got was caused by the fact that your UPDATE statement was not ended with a ;. But even after fixing that, you would have gotten errors, because the column names (in the IF part) can't be referenced like that.

And finally the trigger would have only worked in case the IF conditions were true, because otherwise the trigger would not return anything. So the return new; needs to be outside the IF statement.

PostgreSQL trigger update average and count when new value in column is added

Properly escape your trigger body. Use dollar quoting
Remove the float argument from your function
Actually perform the update in your function
Update the correct table (you are updating the nb_score table in your example which does not have a trigger on it, therefore it will not fire off the trigger)
Specify a relationship between mark and nb_score. As of now the trigger function below updates all nb_score rows, not just the one that is related to the inserted row in mark.;
Your function return type must be trigger

CREATE OR REPLACE FUNCTION FunctionUpdateScore() RETURNS trigger AS $$
BEGIN
 UPDATE nb_score
    SET 
        nb_score=COUNT(score),
        ag_score=AVG(score)
    FROM mark;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER TriggerUpdateScore
AFTER INSERT ON mark FOR EACH ROW EXECUTE PROCEDURE FunctionUpdateScore();

Trigger for Boolean Update (Postgresql)

OLD refers to the data that where before the update and NEW to the updated values.
As you only check the OLD.col_commit data, that is FALSE, you never will get a log entry.

so simply change the trigger to check for NEW.col_commit

Belayer is right, this should be an AFTER UPDATE TRIGGER vecause, it will only be run when the update was commited, a BEFORE UPDATE TRIGGER will run before the commit and could change the data

CREATE OR REPLACE FUNCTION commitment_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
    BEGIN
        IF NEW.col_commit = TRUE THEN
            INSERT INTO commitment(player_id, committed_date)
            VALUES (OLD.player_id, now());
        END IF;
        
        RETURN NEW;
    END;
$$;

CREATE TRIGGER commitment_trigger
AFTER UPDATE
ON player
FOR EACH ROW
EXECUTE PROCEDURE commitment_log();

see sample fiddle

Of course, if you want only to capture the changes from FALSE to TRUE, you need also to check the OLD value

CREATE OR REPLACE FUNCTION commitment_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
    BEGIN
        IF NEW.col_commit = TRUE AND OLD.col_commit = FALSE THEN
            INSERT INTO commitment(player_id, committed_date)
            VALUES (OLD.player_id, now());
        END IF;
        
        RETURN NEW;
    END;
$$;

CREATE TRIGGER commitment_trigger
AFTER UPDATE
ON player
FOR EACH ROW
EXECUTE PROCEDURE commitment_log();

Postgres rule or trigger to update multiple rows after update of single row

Use an AFTER trigger:

create table loc_tbl (id integer, x_loc numeric);
insert into loc_tbl values (1000,12.7), (1500,13.2), (1001,12.7), (1502,13.2), (1002,12.8);

CREATE OR REPLACE FUNCTION public.x_loc_fnc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.x_loc != OLD.x_loc THEN
        UPDATE 
            loc_tbl
        SET 
            x_loc = NEW.x_loc
        WHERE 
            left(NEW.id::varchar, 3) = left(id::varchar, 3);
    END IF;
RETURN null;
END;

$function$
;

CREATE TRIGGER 
    loc_trg 
AFTER UPDATE ON 
    loc_tbl 
FOR EACH ROW EXECUTE FUNCTION 
    x_loc_fnc();

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1000 |  12.7
 1500 |  13.2
 1001 |  12.7
 1502 |  13.2
 1002 |  12.8

UPDATE loc_tbl SET x_loc = 12.9 WHERE id = 1000;
UPDATE 1

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1500 |  13.2
 1502 |  13.2
 1001 |  12.9
 1002 |  12.9
 1000 |  12.9

Postgresql trigger function occasionally fails to update its target correctly – possible race condition?

Yes, there is a race condition. If the last two tasks complete at about the same time, the trigger functions can run concurrently. Since the trigger runs as part of the transaction, and the transactions are both not committed yet, none of the trigger functions can see the data modifications made by the other transaction. So each believes there is still a task open.

You could use an advisory lock to make sure that that cannot happen: right before the SELECT count(*) ..., add

SELECT pg_advisory_xact_lock(42);

That makes sure that no session will execute the query while another session that has already executed the query is still not committed, because the lock is held until the end of the transaction.

Postgresql Update Trigger