asked on

10777 read from table stage TO INSERT two table (with triggers)

hola expertos

La informacion que necesito para llenar las 2 tablas(TA_VENTA_PRODUCTO_CENTRO y TA_VENTA_MES) se encuentra en una tabla stage

El valor de esta columna IDVENTAPRODUCTOCENTRO debe ser el mismo para ambas tablas (TA_VENTA_PRODUCTO_CENTRO y TA_VENTA_MES)

Como genero los valores para esa columna?, actualmente ambas tablas tienen un triggers

CREATE OR REPLACE TRIGGER TGR_VENTA_PRODUCTO_CENTRO
BEFORE INSERT ON TA_VENTA_PRODUCTO_CENTRO
FOR EACH ROW
BEGIN
SELECT SEQ_idVentaProductoCentro.Nextval into :new.idVentaProductoCentro FROM DUAL;
END;

CREATE OR REPLACE TRIGGER TGR_VENTA_MES_INSERT
BEFORE INSERT ON TA_VENTA_MES
FOR EACH ROW
BEGIN
SELECT SEQ_idVentaMes.Nextval into :new.idVentaMes FROM DUAL;
END;

Este es una ejemplo con otros datos (table stage)
SalesOrderID SalesOrderNumber ProductID OrderQty UnitPrice LineTotal
43659 SO43659 776 1 2024.994 2024.994000
43659 SO43659 777 3 2024.994 6074.982000
43660 SO43660 762 1 419.4589 419.458900
43660 SO43660 758 1 874.794 874.794000

Tablas finales:
Tabla 1
43659 SO43659
43660 SO43660

Tabla 2
43659 776 1 2024.994 2024.994000
43659 777 3 2024.994 6074.982000
43660 762 1 419.4589 419.458900
43660 758 1 874.794 874.794000
estructTable.txt

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enrique_aeo

ASKER

Inicialmente las tablas se deberian llenar por una aplicacion web, es por eso que tiene triggers.
Pero habra una carga inicial de informacion y luego se llenara por la aplicacion.

Este es una ejemplo de la informacion llenada por la aplicacion

select vpc.IDVENTAPRODUCTOCENTRO, vpc.IDPRODUCTO, vm.idVentaMes,vm.idmes, vm.millares, vm.costofijo
from TA_VENTA_PRODUCTO_CENTRO vpc
       inner join TA_VENTA_MES vm on vpc.IDVENTAPRODUCTOCENTRO = vm.IDVENTAPRODUCTOCENTRO
order by 1 desc

46      12932      445      1      1200      6
46      12932      446      2      1900      8
46      12932      448      4      0      0
46      12932      447      3      0      0
46      12932      450      6      0      0
46      12932      452      8      0      0
46      12932      454      10      0      0
46      12932      456      12      0      0

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enrique_aeo

ASKER

There will be an initial load and then only by the web application will be made

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enrique_aeo

ASKER

Then the ID should already be generated in the table_stage?

slightwv (䄆 Netminder)

>>Then the ID should already be generated in the table_stage?

If my understanding is correct:
Looks like IDVENTAPRODUCTOCENTRO is in the staging table so no need for a trigger.

You will be controlling the inserts so you can call the sequence for TA_VENTA_MES_ANIO manually so no need for the trigger.

slightwv (䄆 Netminder)

Even if IDVENTAPRODUCTOCENTRO isn't in the staging table, same process I posted above for TA_VENTA_MES_ANIO. You will control the inserts from the staging table to the regular tables so, just call that sequence and not rely on the trigger.