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
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
From your other question:
The staging table has everything you need for the two tables. Why do you need the triggers on the two tables to generate an ID?

If I understand:
The SalesOrderID in the staging table will become IDVENTAPRODUCTOCENTRO from TA_VENTA_PRODUCTO_CENTRO and IDVENTAPRODUCTOCENTRO in TA_VENTA_MES_ANIO

Then you can use a sequence for IDVENTAMESANIO.

Is this correct?
enrique_aeoAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Can you please post in English?


Are you asking how to load from the staging table and not have the triggers change values?

You might be able to disable the triggers to load from the staging table but this can cause serious problems if the application is running at the same time as the load.

You might need to redesign the application to call the sequence.nextval directly and not rely on triggers.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

enrique_aeoAuthor Commented:
There will be an initial load and then only by the web application will be made
slightwv (䄆 Netminder) Commented:
>>There will be an initial load and then only by the web application will be made

Then disable the triggers, do the load, enable the triggers.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
Then the ID should already be generated in the table_stage?
slightwv (䄆 Netminder) Commented:
>>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) Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.