Create total sales (value) per customer (SQL)

Question

I need to make a procedure to install the column cli_TotalCompras of the customer table with the total (in value) of the customer’s purchases.

On the table vendaProduto have the value (vpr_ValorUnit), the amount (vpr_Quantidade) and the sale ID (ven_ID) associated with the table venda which contains the customer you purchased (cli_ID).

Like I should do?

Table cliente:

create table cliente (
cli_ID           int not null constraint PKCliente primary key,
cli_Nome         varchar(80) not null,
cli_Sexo         char(01) check (cli_Sexo in('M', 'F')),
cli_Nascimento   date Not null,
cid_ID           int not null, 
cli_Email        varchar(100),
cli_Fixo         varchar(11),
cli_Celular      varchar(11),
cli_TotalCompras decimal(10,2);
constraint      fkCliCid foreign key(cid_Id) references cidade(cid_Id));

Table venda:

create table venda (
ven_ID   int not null identity (1,1) constraint PKVenda primary key,
cli_ID   int not null,
fun_ID   int not null, 
ven_Data date not null,
constraint fkVenCli foreign key (cli_ID) references cliente(cli_ID),
constraint fkVenFun foreign key (fun_ID) references funcionario(fun_ID));

Table vendaProduto:

create table vendaProduto (
vpr_ID          int identity (1,1) constraint PKVendaProduto primary key,
ven_ID          int not null,
liv_ID          int not null, 
vpr_Quantidade  int,
vpr_ValorUnit   decimal(15,2),
constraint fkItvVen foreign key (ven_ID) references venda(ven_ID),
constraint fkItvliv foreign key (liv_ID) references livro(liv_ID));

Answer 1

Felipe, regarding the use of procedure Trigger, I suggest you avoid as much as possible their use for needs that can be resolved in the app.

But if what you posted is an academic exercise, and it is requested that you implement through procedure Trigger, then it is necessary to create 3 procedures Trigger, one for each action. Or one can implement the treatment of the three actions in a single procedure Trigger.

Sales treatment

-- código #2
CREATE TRIGGER INSERT_vendaProduto
               on vendaProduto
               after INSERT as
begin
-- verifica se há algo a tratar
declare @NL int;
set @NL= (SELECT Count(*) from (SELECT top (2) * from INSERTED) as I);
IF @NL = 0
  return;

-- trata um ou mais itens de vendas, para um ou mais clientes
UPDATE C
  set cli_TotalCompras+= (I.vpr_Quantidade * I.vpr_ValorUnit)
  from Cliente as C
       inner join venda as V on V.cli_ID = C.cli_ID
       inner join INSERTED as I on I.ven_ID = V.ven_ID;
end;
go

Treatment of change in quantity or value of item sold

-- código #3
CREATE TRIGGER UPDATE_vendaProduto
               on vendaProduto
               after UPDATE as
begin
-- verifica se há algo a tratar
declare @NL int;
set @NL= (SELECT Count(*) from (SELECT top (2) * from INSERTED) as I);
IF @NL = 0
  return;

-- encerra se não houve tentativa de alteração nas colunas de quantidade ou valor unitário
IF not (UPDATE(vpr_Quantidade) or UPDATE(vpr_ValorUnit))
  return;

-- trata um ou mais itens de vendas, para um ou mais clientes
UPDATE C
  set cli_TotalCompras+= ((I.vpr_Quantidade * I.vpr_ValorUnit) - (D.vpr_Quantidade * D.vpr_ValorUnit))
  from Cliente as C
       inner join venda as V on V.cli_ID = C.cli_ID
       inner join DELETED as D on D.ven_ID = V.ven_ID
       inner join INSERTED as I on I.ven_ID = V.ven_ID
end;
go

Return (cancellation) treatment of sold item

-- código #4
CREATE TRIGGER DELETE_vendaProduto
               on vendaProduto
               after DELETE as
begin
-- verifica se há algo a tratar
declare @NL int;
set @NL= (SELECT Count(*) from (SELECT top (2) * from DELETED) as D);
IF @NL = 0
  return;

-- trata um ou mais itens de vendas, para um ou mais clientes
UPDATE C
  set cli_TotalCompras-= (D.vpr_Quantidade * D.vpr_ValorUnit)
  from Cliente as C
       inner join venda as V on V.cli_ID = C.cli_ID
       inner join DELETED as D on D.ven_ID = V.ven_ID
end;
go

.

Answer 2

If what you need is a stored procedure that should be called by the app at the end of each sale (that is, after all the items from the sale have been registered), here is a suggestion:

-- código #1 v2
CREATE PROCEDURE VendasCliente
                 @pIDvenda int
as
with Soma_vendas as (
SELECT V.cli_ID, Sum(VP.vpr_Quantidade * VP.vpr_ValorUnit) as totalVendas
  from venda as V 
       inner join vendaProduto as VP on VP.ven_ID = V.ven_ID
  where V.ven_ID = @pIDvenda
  group by V.cli_ID
)
UPDATE C
  set cli_TotalCompras+= S.totalVendas
  from cliente as C
       inner join Soma_vendas as S on S.cli_ID = C.cli_ID;
go

---