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

Viewed 1994

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.

1 Answers
Related