Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-10-07
8
239 Views
Last Modified: 2014-10-08
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
0
Comment
Question by:enrique_aeo
  • 5
  • 3
8 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40366353
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?
0
 

Author Comment

by:enrique_aeo
ID: 40366398
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
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40366413
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:enrique_aeo
ID: 40366423
There will be an initial load and then only by the web application will be made
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40366431
>>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.
0
 

Author Comment

by:enrique_aeo
ID: 40366463
Then the ID should already be generated in the table_stage?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40366474
>>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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40366483
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question