CODEWITHSUNDEEP
sqldatabasepostgresqltriggers
Ivan
Ivan

Reputation: 13

Trigger function updates all rows instead of one

I have table "orders" and I want to change column "order_status" automatically when I add a value in column "tracking_code" (default value is null) but my trigger function changes all rows in column "order_status" I tried to change if statement in the function, but I hadn't any success. please help me with this question! this is my table "orders":

id | order_status_id | tracking_code
----+-----------------+---------------
 12 |               2 | 123456
  9 |               2 |
  6 |               2 |
  7 |               2 |
 10 |               2 |
  8 |               2 |
 11 |               2 |

and table "order_statuses":

 id |   status
----+-------------
  1 | QUEUE
  2 | IN_PROGRESS
  3 | IN_DELIVERY
  4 | DELIVERED
  5 | CANCELED
  6 | RETURNED

this is my trigger and function:

CREATE or REPLACE FUNCTION status_function() RETURNS VOID AS $$
BEGIN
    UPDATE public.orders SET order_status_id = public.order_statuses.id
    FROM public.order_statuses
    WHERE status = 'IN_DELIVERY';
END
$$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION status_update() RETURNS TRIGGER AS $$
BEGIN
    IF (old.tracking_code != new.tracking_code) THEN
        PERFORM status_function();
        RETURN new;
    ELSE
        RETURN old;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER change_status
    AFTER UPDATE
        OF tracking_code
    ON orders
    FOR EACH ROW
EXECUTE PROCEDURE status_update();

Upvotes: 0

Views: 38

Answers (2)

Mike Organek
Mike Organek

Reputation: 12494

Please try something like this to restrict the update to only the current row:

CREATE or REPLACE FUNCTION status_function(_id int) RETURNS VOID AS $$
BEGIN
    UPDATE public.orders SET order_status_id = public.order_statuses.id
      FROM public.order_statuses
     WHERE status = 'IN_DELIVERY'
       AND id = _id;
END
$$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION status_update() RETURNS TRIGGER AS $$
BEGIN
    IF (old.tracking_code != new.tracking_code) THEN
        PERFORM status_function(old.id);
        RETURN new;
    ELSE
        RETURN old;
    END IF;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Mafor
Mafor

Reputation: 10711

You need to pass the updated order id to the status_function:

CREATE or REPLACE FUNCTION status_function(bigint) RETURNS VOID AS $$
BEGIN
    UPDATE public.orders SET order_status_id = public.order_statuses.id
    FROM public.order_statuses
    WHERE status = 'IN_DELIVERY' AND public.orders.id = $1;
END
$$ LANGUAGE plpgsql;

And then, in the status_update:

PERFORM status_function(old.id);

Upvotes: 0

Related Questions