?
Solved

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

Posted on 2014-10-07
8
Medium Priority
?
247 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
[X]
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
  • 5
  • 3
8 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 77

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 77

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
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
Suggested Courses

771 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