Solved

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

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

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Row_number in SQL 6 45
oracle sqlplus query delimiter 8 39
Excess Redo 3 32
looking for guidance on Oracle Sql Formatting standards 9 30
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

733 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