How to update column value based on other column change within same table without any primary key column in table

Question

I have a table called 'custom_manual_edit' with columns 'name', 'builder' and 'flag' in which there is no column with primary key.I have written a trigger when user update any change in builder column and that trigger will invoke a function that should update a flag column value to 10 for record for which builder value is changed below is my trigger

CREATE TRIGGER builder_update_trigger_manual_custom_edits
AFTER UPDATE
ON edmonton.custom_manual_edit
FOR EACH ROW
WHEN (((old.builder)::text IS DISTINCT FROM (new.builder)::text))
EXECUTE PROCEDURE 
edmonton.automated_builder_update_trigger_manual_custom_edits();

and my function

  CREATE OR REPLACE FUNCTION 
edmonton.automated_builder_update_trigger_manual_custom_edits()
RETURNS trigger AS
$BODY$
DECLARE
e record;

BEGIN
IF NEW.builder <> OLD.builder THEN
    EXECUTE FORMAT('UPDATE edmonton.custom_manual_edit set builder_edit_flag = 10;
END IF;
RETURN NEW;

 END 
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I know this will update entire table flag column to 10 but how to update flag value for records for which builder value is changed.

Answer 1

Please check the documentation: 36.1. Overview of Trigger Behavior

Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:

• It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion, modification, or deletion of a particular table row).

• For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated. This allows the trigger function to modify the row being inserted or updated.

A row-level BEFORE trigger that does not intend to cause either of these behaviors must be careful to return as its result the same row that was passed in (that is, the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers).

According to the above you must:

1. declare the trigger as BEFORE UPDATE, not AFTER UPDATE
2. changebuilder_edit_flag column value directly in NEW row instead of firing UPDATE statement

---

CREATE TRIGGER builder_update_trigger_manual_custom_edits
BEFORE UPDATE
ON edmonton.custom_manual_edit
FOR EACH ROW
.....
.....

---

CREATE OR REPLACE FUNCTION 
edmonton.automated_builder_update_trigger_manual_custom_edits()
.....
.....

BEGIN
IF NEW.builder <> OLD.builder THEN
    NEW.builder_edit_flag = 10;
END IF;
RETURN NEW;
.....
.....