Click here to Skip to main content
15,894,240 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi guys,

I have been struggling with a trigger for some time now.

I get the error in the title when compiling my trigger.
I am not quite sure about whether this trigger is correct or not.

I hope some help from you to make this trigger in a working condition by suggesting appropriate changes.
error is on the orange line.



SQL
CREATE OR REPLACE TRIGGER ERPTRAIN.TRG_MERCH_CUST_CHECK

BEFORE INSERT

ON ERPTRAIN.OM_MERCH_CUST

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE


CURSOR C1(C_CUST_CODE IN VARCHAR2,C_COMP_CODE IN VARCHAR2,C_DIVN_CODE

VARCHAR2,C_FM_DT IN DATE,C_TO_DT IN DATE)


IS


SELECT 1

FROM OM_MERCH_CUST,OM_MERCHINDISER_COMP_DIVN

WHERE MC_CUST_CODE = C_CUST_CODE

AND MCD_SYSID = MC_MCD_SYSID

AND (MC_FM_DT BETWEEN C_FM_DT AND C_TO_DT

OR MC_TO_DT BETWEEN C_FM_DT AND C_TO_DT)

AND MCD_COMP_CODE = C_COMP_CODE

AND MCD_DIVN_CODE = C_DIVN_CODE;



result NUMBER(1) := 0;



BEGIN

IF C1%ISOPEN

THEN

CLOSE C1;

END IF;

error on this line



OPEN C1(:new.MC_CUST_CODE, :new.MC_MCD_SYSID);




FETCH C1 into result;


CLOSE C1;


IF result <> 0 or result <> null then

RAISE_APPLICATION_ERROR("This Record Already Exist");

END IF;



END;
Posted
Updated 26-Oct-10 1:28am
v5
Comments
Dalek Dave 25-Oct-10 4:26am    
Edited for Grammar, Syntax and Readability. (I wasn't going to retype the code!).
E.F. Nijboer 26-Oct-10 10:31am    
I cannot see the orange line. Also, it would help if you explained what the trigger should do.

1 solution

Couple of points. The line
OPEN C1(:new.MC_CUST_CODE, :new.MC_MCD_SYSID);
doesn't match your cursor declaration. The declaration also has a division code and two dates. Even when you fix this though, you will likely still have an error on your cursor. I could be wrong, but a trigger on a table is not allowed to perform SQL on itself, even if it is a select. The reason for this is because of possibly starting a never ending loop.
From the error message you are trying to raise, why can you not define the appropriate referential integrity rules to enforce unique keys?
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900