PostgreSQL is updating all rows

Question

Hello im trying to create a stock database for cars having 2 tables, carros and faturas, soo carros is the name of the car(veiculo) and how many cars i have in stock(quantidade) and how many cars i sold(vendidos), and the table faturas has the id and the car name(veiculo) the rest doesnt matter of my objective. My objective is after i insert on table faturas the car name(veiculo) it goes to the carros table and it adds +1 to the vendidos of that car, but for now it is updating all my vendidos row. Here it goes my code and i hope you can help me, Thanks.

CREATE TABLE carros (
veiculo VARCHAR(10) not null, 
quantidade INTEGER not null, 
vendidos INTEGER, 
PRIMARY KEY (veiculo));

CREATE TABLE faturas (
id serial not null,
veiculo VARCHAR(10) NOT NULL,
matricula VARCHAR(10) NOT NULL,
nome CHAR(30) NOT NULL,
contacto VARCHAR(15) NOT NULL,
valor INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (veiculo) REFERENCES carros(veiculo));

    CREATE OR REPLACE FUNCTION public.stockupdate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin 
    IF EXISTS(select c.veiculo from carros c, faturas f where c.veiculo = f.veiculo limit 1) THEN 
        UPDATE carros SET vendidos = vendidos + 1 from faturas where carros.veiculo = faturas.veiculo;
    end if;
    return new;
end;
$function$
;

CREATE OR REPLACE FUNCTION public.stockupdate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin 
    IF EXISTS(select c.veiculo from carros c, faturas f where c.veiculo = f.veiculo limit 1) THEN 
        UPDATE carros SET vendidos = vendidos + 1 from faturas where carros.veiculo = faturas.veiculo;
    end if;
    return new;
end;
$function$
;

create trigger stockupdatetrigger
before insert
on faturas 
for each row
execute procedure stockupdate();


INSERT INTO carros
(veiculo, quantidade, vendidos)
VALUES('Opel', 10, 0);

INSERT INTO carros
(veiculo, quantidade, vendidos)
VALUES('Fiat', 10, 0);

INSERT INTO public.faturas
(veiculo, matricula, nome, contacto, valor)
VALUES('Opel', 'ABC 123', 'Ruben', '226-255243', 15000);

CREATE TABLE faturas (
    id serial not null,
    veiculo VARCHAR(10) NOT NULL,
    matricula VARCHAR(10) NOT NULL,
    nome CHAR(30) NOT NULL,
    contacto VARCHAR(15) NOT NULL,
    valor INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (veiculo) REFERENCES carros(veiculo)
);

CREATE OR REPLACE FUNCTION public.stockupdate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin 
    IF EXISTS(select c.veiculo from carros c, faturas f where c.veiculo = f.veiculo limit 1) THEN 
        UPDATE carros SET vendidos = vendidos + 1 from faturas where carros.veiculo = faturas.veiculo;
    end if;
    return new;
end;
$function$
;

create trigger stockupdatetrigger
before insert
on faturas 
for each row
execute procedure stockupdate();


INSERT INTO carros
(veiculo, quantidade, vendidos)
VALUES('Opel', 10, 0);

INSERT INTO carros
(veiculo, quantidade, vendidos)
VALUES('Fiat', 10, 0);

INSERT INTO public.faturas
(veiculo, matricula, nome, contacto, valor)
VALUES('Opel', 'ABC 123', 'Ruben', '226-255243', 15000);

GMB · Accepted Answer · 2020-09-01 15:02:50Z

1

You seem to be overcomplicating things here. I think the trigger function you wanted to write is just:

create or replace function public.stockupdate()
    returns trigger
    language plpgsql
as $$
begin 
    update carros set vendidos = vendidos + 1 where veiculo = new.veiculo;
    return new;
end;
$$;

Pseudo-table new can be used to access the row that is being inserted. You can use it to filter the carros table and update the correspoding record, if any

answered Sep 1, 2020 at 15:02

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL is updating all rows

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