Create total sales procedure (value) per customer

Question

Create total sales procedure (value) per customer

Navigation

#1 by (-1 votes)
#2 by (-1 votes)

2

I need to make a procedure that instantiates the cli_TotalCompras column of the client table with the customer's total (in value).

In the vendaProduto table I have the value ( vpr_ValorUnit ), the quantity ( vpr_Quantidade ), and the sales ID ( ven_ID ) associated with table venda that contains the customer you purchased ( cli_ID ).

How should I do it?

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));

sql sql-server procedure trigger

asked by anonymous 26.10.2016 / 00:22

2 answers

Error When Adding Controller in ASP.NET MVC How to select the content of an input in ng-focus?

score -1 · Answer 1

If what you need is a stored procedure that must be called by the application at the end of each sale (ie after all the items in 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

score -1 · Answer 2

Felipe, regarding the use of trigger procedure, I suggest you avoid using them as much as possible for needs that can be solved in the application.

But if what you posted is an academic exercise, and you are asked to implement it by trigger , then you need to create 3 trigger procedures, one for each action . Or you can implement the treatment of the three actions in a single trigger procedure.

Sales Processing

-- 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 item sold

-- 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

.