Home » SQL & PL/SQL » SQL & PL/SQL » Problem with calling statement level after insert trigger (oracle 10g,Windows XP)
Problem with calling statement level after insert trigger [message #350069] |
Wed, 24 September 2008 00:32 |
joebunny
Messages: 18 Registered: September 2008
|
Junior Member |
|
|
Hi All,
I have two tables and I upload data from an external source in both the tables.
Let the two tables be A and B.
Now the scenario is as follows.
First I upload data from an external source into table A.
Then I upload data from external source into table B. But as soon as I upload data into table B I want that depending on certain conditon of certain columns in table A, some columns in table B be updated.
for eg if table A has column named 'salary' and table B has column named 'type'.
When I upload data in table A and then into B my column named 'type' in table B should be updated to rich,medium or poor depending on the value of salary in table A for employees which are commom in both table A and B.
I have wriiten a stored procedure for updating the column in table B and it is woring fine. But the problem I am facing is in calling the procedure. Since I want to update the column in table B automatically after data is loaded in it I have written a statement level after insert trigger on table B and when I try to load bulk data in my tabel B I get an error stating "
SQL*Loader-937: parallel load requested and tablename has
enabled triggers or constraints" and my data itself fails to load let alone procedutre calling and column updation.
Can anybody suggest where am I going wrong.
Any help would be appreciated.
Thanks in advance.
|
|
|
|
Re: Problem with calling statement level after insert trigger [message #350082 is a reply to message #350080] |
Wed, 24 September 2008 01:24 |
joebunny
Messages: 18 Registered: September 2008
|
Junior Member |
|
|
Hi Michel,
Thanks a lot for your reply.
Actually I am loading the data in the tables from external .txt files and I have written control files for that.
Could you explain me what is the problem that my statement level after insert trigger isn't getting called and I get the error that I have mentioned.
I believe statement level triggers are used when you want to perform a certain operation once after a bulk insert in the table and this is exactly what I am doing.
Thanks and regards,
Joebunny
|
|
|
|
Re: Problem with calling statement level after insert trigger [message #350093 is a reply to message #350091] |
Wed, 24 September 2008 02:11 |
joebunny
Messages: 18 Registered: September 2008
|
Junior Member |
|
|
Hi Michel,
Thanks a lot again for your reply!
I have been able to resolve the parallel load error by removing PARALLEL=true from my bat file that I run to execute the control file.
Now atleast my data is getting uploaded in tha table but the triggers it seems is not getting fired because the procedure that I am calling from my trigger is not getting executed and hence the columns aren't getting updated.
I am posting the code for my trigger and the procedure .
Trigger code
CREATE OR REPLACE TRIGGER datasource_trigger
after insert on datasource_test
begin
DATASOURCE_TEST_PROC;
end;
/
Code for my procedure
CREATE OR REPLACE procedure DATASOURCE_TEST_PROC as
begin
UPDATE DATASOURCE_TEST SET HNI_TYPE = 'Normal' WHERE DATASOURCE_TEST.GROSSANNUALPREMIUM < 50000;
UPDATE DATASOURCE_TEST SET HNI_TYPE = 'HNI' WHERE DATASOURCE_TEST.GROSSANNUALPREMIUM BETWEEN 50000 AND 499999 ;
UPDATE DATASOURCE_TEST SET HNI_TYPE = 'Super HNI' WHERE DATASOURCE_TEST.GROSSANNUALPREMIUM > 499999 ;
COMMIT;
end;
/
My table script is as follows
CREATE TABLE BSLDATASOURCE_TEST
(
GROSSANNUALPREMIUM NUMBER,
HNI_TYPE NVARCHAR2(20)
)
When I try to execute the procedure from sql plus it runs fine and updates the HNI_TYPE column properly.
But when I try calling the procedure from my statementlevel after insert trigger it isn't getting executed.
Could you explain what may be the problem.
Thanks and regards,
Joebunny
|
|
|
|
Re: Problem with calling statement level after insert trigger [message #350133 is a reply to message #350123] |
Wed, 24 September 2008 03:12 |
joebunny
Messages: 18 Registered: September 2008
|
Junior Member |
|
|
Hi rajavu1
thanks for your reply.
Quote: |
There will start debates on whether coding such trigger is proper or not .
|
Could you please explain what is wrong with coding such a trigger.
Quote: |
Anyway as a temporary solution , i prefer to remove or comment the COMMIT statement from your Procedure .
|
I tried removing the commit statement from my procedure but then my HNI_TYPe column isn't getting updated though I get the message that PL/SQL procedure successfully completed afer runnung the command SQL> EXEC DATASOURCE_TEST_PROC;
Quote: |
And morover , This functionality can also be implemented through SQL LOADER . Even I prefer that method.
|
Can you please explain how would I acheive that.
It wont be a bad idea I guess, since I am uploading the data in my table using a control file and if I am able to do that it will be very good.
Thanks and regards,
Joebunny
[Updated on: Wed, 24 September 2008 03:14] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: Problem with calling statement level after insert trigger [message #350171 is a reply to message #350155] |
Wed, 24 September 2008 05:11 |
joebunny
Messages: 18 Registered: September 2008
|
Junior Member |
|
|
Hi michel.
Thanks for all the help from you people.
I think I am really stuck up with this thing.
Hope that I will be able to do it.
This time I am posting my all the scripts for the tables as well as the triggers and the procedure.
Hope you get it and be able expplain me what am I supposed to do..
My table scripts are as follows.
Script for datasource_test table
CREATE TABLE DATASOURCE_TEST
(
POLICYNO NUMBER,
POLICYISSUEDATE DATE,
POLICYINFORCEDATE DATE,
APPLICATIONCODE NVARCHAR2(20),
POLICYSTATUS NVARCHAR2(20),
AGENTID NVARCHAR2(20),
SERVICINGBRANCHID NUMBER,
BRANCHNAME NVARCHAR2(100),
AURASTATUS NUMBER,
POLICYREJECTIONREASON NVARCHAR2(50),
APPLICATIONBRANCHRECVDATE DATE,
APPLICATIONSIGNDATE DATE,
APPLICATIONRECVDATE DATE,
PAYMENTMETHOD NVARCHAR2(40),
PAYMENTFREQUENCY NUMBER,
GROSSANNUALPREMIUM NUMBER,
PLANID NVARCHAR2(40),
PLANDESCRIPTION NVARCHAR2(100),
SALESCHANNELCD NVARCHAR2(10),
SALESCHANNEL NVARCHAR2(100),
INITIALPREMIUM NUMBER,
UWDECDATE DATE,
MAIL_DATE DATE,
CVG_UWGDECN_CD NVARCHAR2(20),
UW_DEC NVARCHAR2(100),
RCP_RECPT_DATE DATE,
AURA_STAT NVARCHAR2(100),
POLICYDELIVERYDATE DATE,
POLICYSTATUSCHANGEDATE DATE,
MEDICAL_NONMEDICALFLAG NVARCHAR2(20),
HNI_TYPE NVARCHAR2(20),
STP_FLAG NVARCHAR2(10),
INPUTACCURACY_NCLEAN NVARCHAR2(10),
UW_NCLEAN NVARCHAR2(10),
LINE_NAME NVARCHAR2(20),
MEDICAL_NCLEAN NVARCHAR2(10)
)
Script for requirementtable_test
CREATE TABLE REQUIREMENTTABLE_TEST
(
POLICYNO NUMBER,
POLICYSTATUS VARCHAR2(20 BYTE),
REQMNT_PPOLICY_CCLIENT_LEVEL VARCHAR2(20 BYTE),
REQMNTID VARCHAR2(20 BYTE),
REQMNTCREATEDATE DATE,
REQMNTSTATUS VARCHAR2(20 BYTE),
REQMNTEFFECTIVEDATE DATE,
TESTDATE DATE,
REQMNTTEXT VARCHAR2(255 BYTE),
REQMNTRECEIVEDDATE DATE,
REQMNTORDEREDDATE DATE,
REQMNTACCEPTEDDATE DATE,
REQMNTCANCELLEDDATE DATE,
REQMNTWAIVEDDATE DATE
)
Script of trigger for calling procedure
CREATE OR REPLACE TRIGGER bsldatasource_trigger
after insert on bsldatasource_test
begin
DATASOURCE_TEST_PROC;
end;
/
Procedure for updating columns
CREATE OR REPLACE procedure DATASOURCE_TEST_PROC as
begin
UPDATE DATASOURCE_TEST SET DATASOURCE_TEST.MEDICAL_NONMEDICALFLAG = 'MEDICAL'
WHERE DATASOURCE_TEST.POLICYNO IN
(SELECT DISTINCT DATASOURCE_TEST.POLICYNO
FROM DATASOURCE_TEST, REQUIREMENTTABLE_TEST
WHERE ((REQUIREMENTTABLE_TEST.REQMNTID = 'FMR')
AND (DATASOURCE_TEST.POLICYNO = REQUIREMENTTABLE_TEST.POLICYNO)
)) AND DATASOURCE_TEST.MEDICAL_NONMEDICALFLAG IS NULL;
UPDATE DATASOURCE_TEST SET DATASOURCE_TEST.MEDICAL_NONMEDICALFLAG = 'NONMEDICAL' WHERE DATASOURCE_TEST.MEDICAL_NONMEDICALFLAG IS NULL;
UPDATE DATASOURCE_TEST SET DATASOURCE_TEST.INPUTACCURACY_NCLEAN = 'NCLEAN'
WHERE DATASOURCE_TEST.POLICYNO IN
(SELECT DISTINCT DATASOURCE_TEST.POLICYNO
FROM DATASOURCE_TEST, REQUIREMENTTABLE_TEST
WHERE ((REQUIREMENTTABLE_TEST.REQMNT_PPOLICY_CCLIENT_LEVEL = 'P'
AND REQUIREMENTTABLE_TEST.REQMNTID NOT IN ('CWA', 'REQ43','PAC')
)
AND (DATASOURCE_TEST.POLICYNO = REQUIREMENTTABLE_TEST.POLICYNO)
)) AND DATASOURCE_TEST.INPUTACCURACY_NCLEAN IS NULL ;
UPDATE DATASOURCE_TEST SET DATASOURCE_TEST.UW_NCLEAN = 'NCLEAN'
WHERE DATASOURCE_TEST.POLICYNO IN
(
SELECT DISTINCT DATASOURCE_TEST.POLICYNO
FROM DATASOURCE_TEST, REQUIREMENTTABLE_TEST
WHERE REQUIREMENTTABLE_TEST.REQMNT_PPOLICY_CCLIENT_LEVEL = 'C'
AND REQUIREMENTTABLE_TEST.REQMNTID NOT IN ('FMR', 'BSL','EKG','PSA','TMECG','TELE','ESR','MICRO','RUA')
AND (DATASOURCE_TEST.POLICYNO = REQUIREMENTTABLE_TEST.POLICYNO)
) AND DATASOURCE_TEST.UW_NCLEAN IS NULL;
UPDATE DATASOURCE_TEST SET DATASOURCE_TEST.MEDICAL_NCLEAN = 'NCLEAN'
WHERE DATASOURCE_TEST.POLICYNO IN
(
SELECT DISTINCT DATASOURCE_TEST.POLICYNO
FROM DATASOURCE_TEST, REQUIREMENTTABLE_TEST
WHERE (REQUIREMENTTABLE_TEST.REQMNT_PPOLICY_CCLIENT_LEVEL = 'C'
AND REQUIREMENTTABLE_TEST.REQMNTID = 'FMR')
AND
(
(REQUIREMENTTABLE_TEST.REQMNTRECEIVEDDATE - DATASOURCE_TEST.applicationbranchrecvdate > 7
AND REQUIREMENTTABLE_TEST.REQMNTRECEIVEDDATE IS NOT NULL
AND DATASOURCE_TEST.applicationbranchrecvdate IS NOT NULL)
OR
(REQUIREMENTTABLE_TEST.REQMNTACCEPTEDDATE - DATASOURCE_TEST.applicationbranchrecvdate > 7
AND REQUIREMENTTABLE_TEST.REQMNTRECEIVEDDATE IS NULL
AND REQUIREMENTTABLE_TEST.REQMNTACCEPTEDDATE IS NOT NULL
AND DATASOURCE_TEST.applicationbranchrecvdate IS NOT NULL)
)
AND (DATASOURCE_TEST.POLICYNO = REQUIREMENTTABLE_TEST.POLICYNO)
) AND DATASOURCE_TEST.MEDICAL_NCLEAN IS NULL;
UPDATE DATASOURCE_TEST SET HNI_TYPE = 'Normal' WHERE DATASOURCE_TEST.GROSSANNUALPREMIUM < 50000 OR DATASOURCE_TEST.GROSSANNUALPREMIUM IS NULL AND DATASOURCE_TEST.HNI_TYPE IS NULL;
UPDATE DATASOURCE_TEST SET HNI_TYPE = 'HNI' WHERE DATASOURCE_TEST.GROSSANNUALPREMIUM BETWEEN 50000 AND 499999 AND DATASOURCE_TEST.HNI_TYPE IS NULL;
UPDATE DATASOURCE_TEST SET HNI_TYPE = 'Super HNI' WHERE DATASOURCE_TEST.GROSSANNUALPREMIUM > 499999 AND DATASOURCE_TEST.HNI_TYPE IS NULL;
UPDATE DATASOURCE_TEST SET STP_FLAG = 0 WHERE DATASOURCE_TEST.INPUTACCURACY_NCLEAN IS NULL AND DATASOURCE_TEST.UW_NCLEAN IS NULL AND DATASOURCE_TEST.MEDICAL_NCLEAN IS NULL AND STP_FLAG IS NULL;
UPDATE DATASOURCE_TEST SET STP_FLAG = 1 WHERE DATASOURCE_TEST.INPUTACCURACY_NCLEAN IS NOT NULL OR DATASOURCE_TEST.UW_NCLEAN IS NOT NULL OR DATASOURCE_TEST.MEDICAL_NCLEAN IS NOT NULL AND STP_FLAG IS NULL;
update uploaddatemaster set uploaddate = to_date(SYSDATE,'dd-MM-YY');
COMMIT;
end;
/
I hope this explains my case.
Please note that I am using control files to upload data in both the tables and so I wanted that as soon as I upload data in my datasource_test table certain columns in the table to be updated as per the conditons mentioned in the procedure.
First I upload data in requirement_test table and then into datasource_test table and I want the statement level after insert trigger to fire and call the procedure datasource_test_proc and update the column as per the condition mentioned in the procedure.
I am really sorry guys for giving you such alot of pain.
Thanks a lot.
regards,
Joebunny
|
|
|
|
Re: Problem with calling statement level after insert trigger [message #350308 is a reply to message #350171] |
Wed, 24 September 2008 12:46 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
What I meant is something like this on a simple example:
SQL> create table a as select * from emp;
Table created.
SQL> create table b as select empno, ename, cast(null as varchar2(10)) as saltype from emp where 1=2;
Table created.
SQL> desc b
Name Null? Type
-------------------------------- -------- ----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10 CHAR)
SALTYPE VARCHAR2(10)
SQL> create table b_ext (empno number(4), ename varchar2(10))
2 organization external (
3 type oracle_loader
4 default directory WORK_DIR
5 access parameters (
6 records delimited by X'0A'
7 nobadfile
8 nologfile
9 nodiscardfile
10 fields terminated by ',' optionally enclosed by '"'
11 missing field values are null
12 (empno, ename)
13 )
14 location ('emp.csv')
15 )
16 reject limit unlimited
17 /
Table created.
SQL> desc b_ext
Name Null? Type
-------------------------------- -------- ----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10 CHAR)
SQL> host type E:\Oracle\Work\MIKA\emp.csv
7369,SMITH
7499,ALLEN
7521,WARD
7566,JONES
7654,MARTIN
7698,BLAKE
7782,CLARK
7788,SCOTT
7839,KING
7844,TURNER
7876,ADAMS
7900,JAMES
7902,FORD
7934,MILLER
SQL> insert into b
2 select be.empno, be.ename,
3 case when a.sal is null then '?'
4 when a.sal < 1000 then 'LOW'
5 when a.sal < 2500 then 'MIDDLE'
6 else 'HIGH'
7 end saltype
8 from b_ext be, a
9 where a.empno (+) = be.empno
10 /
14 rows created.
SQL> select * from b ;
EMPNO ENAME SALTYPE
---------- ---------- ----------
7369 SMITH LOW
7499 ALLEN MIDDLE
7521 WARD MIDDLE
7566 JONES HIGH
7654 MARTIN MIDDLE
7698 BLAKE HIGH
7782 CLARK MIDDLE
7788 SCOTT HIGH
7839 KING HIGH
7844 TURNER MIDDLE
7876 ADAMS MIDDLE
7900 JAMES LOW
7902 FORD HIGH
7934 MILLER MIDDLE
14 rows selected.
B is loaded from the external file and at the same time the column SALTYPE is updated depending on the value from table A.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sat Nov 09 14:29:09 CST 2024
|