Home » SQL & PL/SQL » SQL & PL/SQL » Trigger is not working (Oracle 9i, Winxp)
Trigger is not working [message #396261] Sun, 05 April 2009 23:22 Go to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I have written a trigger and the code is as follows:

CREATE OR REPLACE TRIGGER trig_target1 AFTER
    INSERT
    ON product_target1
    FOR EACH ROW
BEGIN
cursor cur_target is
select DEALER_CONTRIBUTION.DISTRIBUTOR_CODE,
 DISTRIBUTOR_INFO_2.DISTRIBUTOR_NAME,
 PRODUCT_TARGET1.YEAR_NO,
 PRODUCT_TARGET1.MONTH_ID,
 DEALER_CONTRIBUTION.PRODUCT_CODE,
 DEALER_CONTRIBUTION.PRODUCT_NAME,
 ((DEALER_CONTRIBUTION.CONTRIBUTION/100)*( PRODUCT_TARGET1.TARGET_QTY))TARGETED_QTY_MONTHLY,
 (((DEALER_CONTRIBUTION.CONTRIBUTION/100)*( PRODUCT_TARGET1.TARGET_QTY))*(DEALER_CONTRIBUTION.INV_RATE))TARGETED_AMT_MONTHLY,
 DEALER_CONTRIBUTION.INV_RATE
FROM DEALER_CONTRIBUTION,PRODUCT_TARGET1,DISTRIBUTOR_INFO_2
WHERE DEALER_CONTRIBUTION.PRODUCT_CODE=PRODUCT_TARGET1.PRODUCT_CODE AND DEALER_CONTRIBUTION.YEAR_NO=PRODUCT_TARGET1.YEAR_NO AND
DISTRIBUTOR_INFO_2.DISTRIBUTOR_CODE=DEALER_CONTRIBUTION.DISTRIBUTOR_CODE;
DISTRIBUTOR_CODE VARCHAR2(20);
DISTRIBUTOR_NAME VARCHAR2(50);
TARGET_YEAR NUMBER(10);
MONTH_ID NUMBER(10);
PRODUCT_CODE VARCHAR2(10);
PRODUCT_NAME VARCHAR2(50);
TARGETED_QTY_MONTHLY NUMBER(10);
TARGETED_AMT_MONTHLY NUMBER(10);
INV_RATE  NUMBER(10,2);
begin
open cur_target;
loop
INSERT INTO DEALER_TARGET_TBL2(DISTRIBUTOR_CODE,
DISTRIBUTOR_NAME,
TARGET_YEAR,
MONTH_ID,
PRODUCT_CODE,
PRODUCT_NAME,
TARGETED_QTY_MONTHLY,
TARGETED_AMT_MONTHLY,
INV_RATE)
VALUES(DISTRIBUTOR_CODE,
DISTRIBUTOR_NAME,
TARGET_YEAR,
MONTH_ID,
PRODUCT_CODE,
PRODUCT_NAME,
TARGETED_QTY_MONTHLY,
TARGETED_AMT_MONTHLY,
INV_RATE);
exit when cur_target%not_found;
end loop;
close cur_target;
end;
end;


the problem is the error that i am receiving is:

Quote:
PLS-00103: Encountered the symbol "CUR_TARGET" when expecting one of the following:

:= . ( @ % ;


I have tried several times to manipulate the trigger as far my knowledge but again and i am getting the same error, so can anyone provide me a suggestion or tips to work it out. I will be glad if you all can support me to get it out from the deadlock situation.


Thanks in Advance
Mahatab Masud
Re: Trigger is not working [message #396263 is a reply to message #396261] Sun, 05 April 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Without knowing which line produces the error & without providing formated code, I refuse to guess at the solution.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Trigger is not working [message #396264 is a reply to message #396261] Sun, 05 April 2009 23:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have written the cursor in the begin section.
Quote:
BEGIN
cursor cur_target is
Change it to declare.

By
Vamsi
Re: Trigger is not working [message #396265 is a reply to message #396261] Sun, 05 April 2009 23:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You never fetch from your cursor.
Also, if you would copy-paste a sqlplus execution with the complete error-message, it would show us WHERE the error occurs.
Re: Trigger is not working [message #396267 is a reply to message #396261] Sun, 05 April 2009 23:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not able to post a correctly written post?

Regards
Michel
Re: Trigger is not working [message #396280 is a reply to message #396261] Mon, 06 April 2009 00:44 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thank You All,
Let me try to solve the problem according to the valuable suggestions provided by you all and sorry Michel yet i have got some problem regarding posting the code, please pardon me. I will let you know shortly as soon as i can
Re: Trigger is not working [message #396296 is a reply to message #396261] Mon, 06 April 2009 01:30 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I have successfully solved the problem raised earlier and now facing with another problem of the following:

Quote:
ORA-04091
ORA-04091:table string.string is mutating, trigger/function may not see it
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.

Now as i am working on the product_target1 and table and trying to manipulate the results using its field in several times. Now is there any other way i can go for. Just suggest me what to do now. Again if the problem is not clear yet tell me i will post it again.
Re: Trigger is not working [message #396297 is a reply to message #396296] Mon, 06 April 2009 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Just suggest me what to do now

Quote:
Action: Rewrite the trigger (or function) so it does not read that table

Regards
Michel
Re: Trigger is not working [message #396298 is a reply to message #396296] Mon, 06 April 2009 01:35 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Read this it may help you
http://asktom.oracle.com/tkyte/Mutate/
Re: Trigger is not working [message #396300 is a reply to message #396261] Mon, 06 April 2009 01:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Within a trigger on TABLE_A, no SQL is permitted against TABLE_A.
Re: Trigger is not working [message #396302 is a reply to message #396300] Mon, 06 April 2009 01:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Sun, 05 April 2009 23:36
BlackSwan wrote on Mon, 06 April 2009 08:36
Within a trigger on TABLE_A, no SQL is permitted against TABLE_A.


Laughing



True? yes?
Re: Trigger is not working [message #396303 is a reply to message #396302] Mon, 06 April 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry you posted at the same time I wanted to quote bonker and so I took the wrong post.
My post wanted to be:
Quote:
bonker wrote on Mon, 06 April 2009 08:35
Read this it may help you
http://asktom.oracle.com/tkyte/Mutate/

Laughing


[Edit: And I deleted my post when I saw it but you saw it before I could delete it...]

[Updated on: Mon, 06 April 2009 01:48]

Report message to a moderator

Re: Trigger is not working [message #396305 is a reply to message #396261] Mon, 06 April 2009 01:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why is it so difficult to post correct PL/SQL syntax when it is enumerated in the manuals found at http:/tahiti.oracle.com?
Re: Trigger is not working [message #396326 is a reply to message #396261] Mon, 06 April 2009 02:30 Go to previous message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I am working on the code and post it in the forum according to the proper formating for your better understanding. Hope it will work now.

Regards,
Mahatab Masud
Previous Topic: Enquiries on searching date using Between function 'merged 3)
Next Topic: pdf (merged)
Goto Forum:
  


Current Time: Sat Dec 10 10:38:02 CST 2016

Total time taken to generate the page: 0.07584 seconds