Postgresql Trigger for a Single Row Column Field

Question

I need some help in fixing my Postgres 9.4 Table Trigger configuration. For the past couple of days I have been trying several different stackoverflow search ideas without success. How can I fix my code to meet the simple requirements described below?

The requirement is to change the color field status based upon the current bus status field value. The color update should only occur for the single row and NOT the entire BusColor table. So when AI_Bus ‘1145’ operational status (BusStatus) is INSERT OR UPDATED, the trigger would change the color field to the proper associated color. Say that the BusStatus for ‘1145’ is updated from ‘NSCH’ to ‘SCH’, then the trigger would change the color value from ‘white’ to ‘yellow’. Below are my Posgresql 9.4 object configurations:

CREATE TABLE  BusColor (
AI_Bus      serial PRIMARY KEY,
BusStatus   character varying NOT NULL,
color       character varying NOT NULL );

SELECT * FROM “MyDB”.”BusColor”;
AI_Bus      BusStatus   color
1145        NSCH        white
1146        SCH         yellow
1147        NSCH        white
1148        OPER        green

CREATE TRIGGER “UpdateBusColor” BEFORE INSERT OR UPDATE ON “BusColor”
FOR EACH STATEMENT EXECUTE PROCEDURE “TrigFuncBusColor”();

CREATE FUNCTION “TrigFuncBusColor”() RETURNS trigger
LANGUAGE plpgsql
AS $$BEGIN
IF (TG_OP = ‘UPDATE’) THEN
    IF (NEW.”BusStatus” <> OLD.”BusStatus”) THEN
        IF NEW.“BusStatus” = ‘NSCH’ THEN
            UPDATE “MyDB”.”BusColor” SET “color” = ‘white’;
            RETURN NEW;
        END IF;

        IF NEW.“BusStatus” = ‘SCH’ THEN
            UPDATE “MyDB”.”BusColor” SET “color” = ‘yellow’;
            RETURN NEW;
        END IF;

         IF NEW.“ID_BusStatus” = ‘OPER’ THEN
            UPDATE “MyDB”.”BusColor” SET “color” = ‘green’;
            RETURN NEW;
         END IF;
   END IF;
END IF;
RETURN NULL;
END;
$$;

You understand the trigger update wrong instead of using the update set .... command you just set the value for the new field like NEW.color = 'somevalue' — Jorge Campos
– Jorge Campos, Commented Mar 27, 2018 at 17:13
I just tried using the UPDATE statement since the NEW & OLD were producing errors: record "new" is not assigned yet. The tuple structure is not-yet-assigned record is indeterminate. This is like version 50 for me...trying all of the crazy stuff on the Internet. — Crimson17
– Crimson17, Commented Mar 27, 2018 at 18:22
Change the trigger from FOR EACH STATEMENT to FOR EACH ROW and use the NEW.field — Jorge Campos
– Jorge Campos, Commented Mar 27, 2018 at 18:37
Thanks Jorge! I switched back to FOR EACH ROW and changed the updates fields back to the original. CREATE TRIGGER “UpdateBusColor4” BEFORE INSERT OR UPDATE OF “BusStatus”, color ON “BusColor” FOR EACH ROW EXECUTE PROCEDURE “TrigFuncBusColor”(); — Crimson17
– Crimson17, Commented Mar 27, 2018 at 19:57
I also changed the trigger function: CREATE FUNCTION “TrigFuncBusColor”() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF TG_OP = ‘UPDATE’ THEN IF (OLD.”BusStatus” <> NEW .”BusStatus”) THEN IF (NEW.“BusStatus” = ‘SCH’) THEN NEW.“color” = ‘yellow’; RETURN NEW; END IF; IF (NEW.“BusStatus” = ‘NSCH’) THEN NEW.“color” = ‘white’; RETURN NEW; END IF; IF (NEW.“BusStatus” = ‘OPER’) THEN NEW.“color” = ‘green’; RETURN NEW; END IF; END IF; END IF; RETURN NULL; END; $$; — Crimson17
– Crimson17, Commented Mar 27, 2018 at 20:01

Ritesh Jha · Accepted Answer · 2018-03-28 16:03:00Z

2

Why you use condition for each bus status, just select the color of replaced status and update the color of all which status is new one.

CREATE FUNCTION “TrigFuncBusColor”() RETURNS trigger
    LANGUAGE plpgsql
    declare newColor varchar;
    AS $$BEGIN
    IF (TG_OP = ‘UPDATE’) THEN
        IF (NEW.”BusStatus” <> OLD.”BusStatus”) THEN
            SELECT "color" INTO newColor FROM “MyDB”.”BusColor” WHERE BusStatus = NEW.“BusStatus” LIMIT 1;
            UPDATE “MyDB”.”BusColor” SET “color” = newColor where BusStatus = NEW.“BusStatus”;
            RETURN NEW;
       END IF;
    END IF;
    RETURN NULL;
    END;

answered Mar 28, 2018 at 16:03

Ritesh Jha

2913 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Trigger for a Single Row Column Field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related