Solved

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

Posted on 2014-10-07
8
228 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
 

Author Comment

by:enrique_aeo
ID: 40366423
There will be an initial load and then only by the web application will be made
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.

 
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

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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now