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 Go to next message
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 #350080 is a reply to message #350069] Wed, 24 September 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ How do you load?
2/ If you use external table, you can modify the data in the query that loads.

Regards
Michel
Re: Problem with calling statement level after insert trigger [message #350082 is a reply to message #350080] Wed, 24 September 2008 01:24 Go to previous messageGo to next message
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 #350091 is a reply to message #350082] Wed, 24 September 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't know I have neither your tables nor your code.
Actually the error is clear:
UL-00937: parallel load requested and %s has enabled triggers or constraints\n
 *Cause:  A request was made for a parallel direct load, but the object to
          be loaded has an enabled constraint (check, referential)
          and/or enabled triggers.
 *Action: Disable the offending enabled constraints and/or triggers
          and retry the parallel direct load.

The other solution is not to do parallel direct load.

But I try to provide you a solution without any trigger and do it in a single query.
Post a test case (create table and sample of data) and I can show you.

Regards
Michel
Re: Problem with calling statement level after insert trigger [message #350093 is a reply to message #350091] Wed, 24 September 2008 02:11 Go to previous messageGo to next message
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 #350123 is a reply to message #350069] Wed, 24 September 2008 02:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There will start debates on whether coding such trigger is proper or not .

Anyway as a temporary solution , i prefer to remove or comment the COMMIT statement from your Procedure .

And morover , This functionality can also be implemented through SQL LOADER . Even I prefer that method.

Thumbs Up
Rajuvan.
Re: Problem with calling statement level after insert trigger [message #350133 is a reply to message #350123] Wed, 24 September 2008 03:12 Go to previous messageGo to next message
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 #350134 is a reply to message #350093] Wed, 24 September 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You cannot commit inside a trigger.
ORA-04092: "cannot %s in a trigger"
 *Cause: A trigger attempted to commit or rollback.
 *Action: Rewrite the trigger so it does not commit or rollback.

Regards
Michel
Re: Problem with calling statement level after insert trigger [message #350136 is a reply to message #350134] Wed, 24 September 2008 03:19 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
I am not commiting inside the trigger.
Though I am using the commit statement in my procedure,
But then I tried removing commit from my procedure but still no difference.The column isn't getting updated.
regards,
Joebunny
Re: Problem with calling statement level after insert trigger [message #350138 is a reply to message #350069] Wed, 24 September 2008 03:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Just EXEC DATASOURCE_TEST_PROC; will not save changes especially when there is no commit inside the procedure . You have to commit explicitly to save the updations .

For implementing in the SQLLDR code , refer the link

CASE IN SQLLDR

CASE AND DECODE

Thumbs Up
Rajuvan.

[Updated on: Wed, 24 September 2008 03:30]

Report message to a moderator

Re: Problem with calling statement level after insert trigger [message #350139 is a reply to message #350136] Wed, 24 September 2008 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
joebunny wrote on Wed, 24 September 2008 10:19
I am not commiting inside the trigger.
Though I am using the commit statement in my procedure,

Procedure that calls the trigger, this is the same thing.

I didn't read your code as I said I think this is not the correct way to do it.

Regards
Michel

Re: Problem with calling statement level after insert trigger [message #350151 is a reply to message #350139] Wed, 24 September 2008 04:19 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi michel,
Could you please explain the correct way to do it..
Regards,
Joebunny
Re: Problem with calling statement level after insert trigger [message #350155 is a reply to message #350151] Wed, 24 September 2008 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 24 September 2008 08:46
Post a test case (create table and sample of data) and I can show you.

Regards
Michel

Re: Problem with calling statement level after insert trigger [message #350156 is a reply to message #350069] Wed, 24 September 2008 04:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Did you try out the way through SQL LOADER ? Isn't it working ?

Thumbs Up
Rajuvan
Re: Problem with calling statement level after insert trigger [message #350171 is a reply to message #350155] Wed, 24 September 2008 05:11 Go to previous messageGo to next message
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 #350177 is a reply to message #350156] Wed, 24 September 2008 05:17 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi rajavu1,
I wish I could do that.
But there are are a number of column that need to be updated as mentioned in my previous post.
I will be really grateful if you could help me.
Re: Problem with calling statement level after insert trigger [message #350308 is a reply to message #350171] Wed, 24 September 2008 12:46 Go to previous messageGo to next message
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
Re: Problem with calling statement level after insert trigger [message #350313 is a reply to message #350308] Wed, 24 September 2008 13:10 Go to previous message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi Michel,
Thanks for your help.
Will surely try this and let u know..
Regards,
Joebunny
Previous Topic: How to trace sql script execution
Next Topic: Using internal procedures and spooling output
Goto Forum:
  


Current Time: Sat Nov 09 14:29:09 CST 2024