create database registro_ventas;

use registro_ventas;

create table ventas(
  empleado_id tinyint not null,
  nombre varchar(25) not null,
  date date not null,
  cantidad decimal(6,2) not null,
  prod_id int not null
);

create table registros(
  empleado_id int not null,
  nombre varchar(25) not null,
  venta_total decimal(6,2) not null,
  prom decimal(6,2) not null
);

create trigger ventas_por_trigger
before insert on ventas
for each row
begin
  declare num_ren integer;
  declare tot_ren integer;
  select count(*) into tot_ren
  from ventas
  where empleado_id=new.empleado_id;
  select count(*) into num_ren
  from registros
  where empleado_id=new.empleado_id;
  
  if num_ren>0 then
    update registros
      set venta_total=new.cantidad+venta_total,
          prom = venta_total/(tot_ren+1)
    where empleado_id=new.empleado_id;
  else
    insert into registros
      (empleado_id,nombre,venta_total,prom)
    values (new.empleado_id,new.nombre,new.cantidad,new.cantidad);
  end if;
end;

insert into ventas values (1,"Juan Perez","2012-10-22",500,123);
insert into ventas values (2,"Maria lopez","2012-10-20",450,12);
insert into ventas values (1,"Juan Perez","2012-10-22",512,13);
insert into ventas values (1,"Juan Perez","2012-10-22",980,11);
insert into ventas values (2,"Maria lopez","2012-10-22",465,13);