Home » RDBMS Server » Server Utilities » Oracle statement trigger issue with SQL loader
| Oracle statement trigger issue with SQL loader [message #73516] |
Thu, 13 May 2004 11:30 |
jimmy
Messages: 21
Registered: November 2000
|
Junior Member |
|
|
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 table9statement) 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. the row level trigger should be fired only! after disable row level trigger, the statement trigger is still fired at row level during SQL loading data.
how to fix this problem? Any ideal? oracel version 91--9.2.0.4.0. OS is win2000.
Please see my table level trigger codes
Thanks
Jimmy
*************
Create Or Replace Trigger FRSATM_Validation
After INSERT ON BA_FRSATM
DECLARE
V_CIRCUIT_ID BA_FRSATM.CIRCUIT_ID%TYPE;
V_SERVICE_TYPE BA_FRSATM.SERVICE_TYPE%TYPE;
V_MEGA_ID BA_FRSATM.MEGA_ID%TYPE;
V_BILL_ID BA_FRSATM.BILL_ID%TYPE;
V_INVOICE_NO BA_FRSATM.INVOICE_NO%TYPE;
V_INVOICE_DATE BA_FRSATM.INVOICE_DATE%TYPE;
V_Install_date BA_FRSATM.INSTALL_DATE%TYPE;
-- Cursor init
Cursor C_FRS IS
SELECT 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_ID2.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, 0, 0,0, 0,0, 0,0,'','','', 0, 0, 0, V_FLATFILEDATE, SYSDATE, '', 'NO FLATFILE DATE PARMETER FOUND IN SYSTEM TABLE LEVEL','PORT');
--A 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;
INSERT INTO AUDIT_VALUE( ID,name,info)VALUES (PK_SQ.NEXTVAL,'FRS TABLE LEVEL','TRIGGER');
END LOOP; -- whole logical condition
Close C_FRS;
END FRSATM_Validation;
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 15:54:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
