|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
|
|||
|
|||
|
Hi friends,
I created 2 triggers for a loading table. one is row level trigger. one is a table level(statement trigger). When I am loading data by SQL loader, I found that table trigger is fired during loading time. the trigger of table level is working with SQL statement during loading . Actually, table level trigger shouild be fired after loaded. how to fix this problem? Please see mu table level trigger codes Thanks Jimmy ************* Create Or Replace Trigger FRSATM_Validation After INSERT ON BA_FRSATM DECLARE PRAGMA AUTONOMOUS_TRANSACTION; V_FRSDATA BA_FRSATM%ROWTYPE; -----othrt declare varivale here -- Cursor init Cursor C_FRS IS SELECT DISTINCT CIRCUIT_ID,MEGA_ID,BILL_ID, SERVICE_TYPE, INVOICE_DATE,Install_date FROM BA_FRSATM Where FlatFileDate = Upper(V_FLATFILEDATE) GROUP BY CIRCUIT_ID,MEGA_ID,BILL_ID, SERVICE_TYPE, INVOICE_NO,INVOICE_DATE,Install_date; BEGIN -- ASSIGN TO BIND VARIABLE BEFORE COURSOR OPEN BEGIN SELECT FLATFILEDATE, Pre_FLATFILEDATE Into V_FLATFILEDATE, V_Pre_FLATFILEDATE From S_frs_flatfiledate; Exception When NO_DATA_FOUND THEN --INSERT INTO AUDIT_VALUE( ID,name,info)VALUES (PK_SQ.NEXTVAL,'NO FLAT DATE FIND','N/A'); INSERT INTO AUDIT_FRSATM2 VALUES (AUDIT_FRS_ID.NEXTVAL,'', V_CIRCUIT_ID,'',V_MEGA_ID,V_BILL_ID, V_SERVICE_TYPE, V_INVOICE_NO, V_INVOICE_DATE, '', '','', '', '', '','', '', '', '', '', '', 0, 0, 0, 0, 0, 0, 0, V_FEATURE_ADMIN_INSTAL_NRC, V_EQUIP_MRC,V_EQUIP_NRC, 0,0, 0,0,'','','', 0, 0, 0, V_FLATFILEDATE, SYSDATE, '', 'NO FLATFILE DATE PARMETER FOUND IN SYSTEM TABLE','PORT'); COMMIT; END; OPEN C_FRS; LOOP FETCH C_FRS INTO V_CIRCUIT_ID,V_MEGA_ID,V_BILL_ID,V_SERVICE_TYPE,V_INVOICE_DATE,V_Install_date ; EXIT WHEN C_FRS%NOTFOUND; IF (SUBSTR(V_CIRCUIT_ID, 1, 1) ='Z' OR SUBSTR(V_CIRCUIT_ID, 1, 1) = 'P') AND V_SERVICE_TYPE ='FRS' AND V_Install_date IS NOT NULL THEN do SQL job END IF; END IF; END LOOP; Close C_FRS; COMMIT; END FRSATM_Validation; |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > trigger issue for loading data by SQL loader |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|