Home » SQL & PL/SQL » SQL & PL/SQL » Problem in Trigger when it is updating (Oracle 10g)
Problem in Trigger when it is updating [message #409383] Mon, 22 June 2009 07:09 Go to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Hi Dear All
I have created one trigger in backend and its compiled successfull I am getting one problem in trigger when i save that form its giving me
Error : Not all varaibles bound

The trigger is below
[CODE]
CREATE OR REPLACE TRIGGER "PARALLEL".ASG_SM_INSATND_TRG
AFTER INSERT OR UPDATE OR DELETE    ON ASG_SM_EMP_KEY
FOR EACH ROW
DECLARE
    CURSOR C1 IS
	    SELECT AM_COMP_CODE, AM_PRC_MONTH, AM_START_DT, AM_END_DT, AM_CLOSED
		FROM ASG_ST_AB_MASTER
		WHERE AM_COMP_CODE = :NEW.EMP_COMP_CODE
		AND NVL(AM_CLOSED,'N') ='N';
	CURSOR C2(M_PROC_MONTH VARCHAR2) IS
	    SELECT COUNT(*)
		FROM ASG_ST_AB_TIMESHEET
		WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE
		AND AT_EDIVN_CODE = :NEW.EMP_DIVISION
		AND AT_EDEPT_CODE = :NEW.EMP_DEPT_CODE
		AND AT_PERIOD = M_PROC_MONTH;
	CURSOR C3 IS
	    SELECT USER_ID
		FROM ASG_ST_AB_USERINFO
		WHERE USER_APPL_DIVISION = :NEW.EMP_DIVISION
		AND USER_APPL_DEPT = :NEW.EMP_DEPT_CODE;

	CURSOR C4(M_PROC_MONTH VARCHAR2) IS
	    SELECT COUNT(*)
		FROM ASG_ST_AB_TIMESHEET
		WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE
		AND AT_EDIVN_CODE = :NEW.EMP_DIVISION
		AND AT_EDEPT_CODE = :NEW.EMP_DEPT_CODE
		AND AT_PERIOD = M_PROC_MONTH
		AND AT_EMP_CODE = :NEW.EMP_CODE;
	CURSOR C5(M_START_DT IN DATE) IS
        SELECT ROWNUM RN,CAL_DATE
		FROM ASG_SM_CALENDAR_NORMAL
		WHERE CAL_DATE >= M_START_DT
		AND CAL_DATE < :NEW.EMP_JOIN_DT;

	C1_ROW C1%ROWTYPE;
	M_MODE VARCHAR2(20);
	M_COUNT NUMBER;
	M_ECOUNT NUMBER;
	M_USERID VARCHAR2(12);
	M_QUERY VARCHAR2(4000);
BEGIN
 	IF INSERTING   THEN
   		m_mode := 'INSERT';
	ELSIF UPDATING  THEN
	    m_mode := 'UPDATE';
	ELSE
		m_mode := 'DELETE';
	END IF;
	IF M_MODE IN ('INSERT','UPDATE') THEN
	    IF NVL(:NEW.EMP_STATUS,'0') = '1' THEN
		    IF C1%ISOPEN THEN CLOSE C1; END IF;
			OPEN C1;
			    FETCH C1 INTO C1_ROW;
		    CLOSE C1;
			M_COUNT := 0;
			IF C1_ROW.AM_PRC_MONTH IS NOT NULL THEN
			    IF C2%ISOPEN THEN CLOSE C2; END IF;
				OPEN C2(C1_ROW.AM_PRC_MONTH);
				    FETCH C2 INTO M_COUNT;
				CLOSE C2;
			END IF;
			IF NVL(M_COUNT,0) != 0 THEN
			    IF C3%ISOPEN THEN CLOSE C3; END IF;
				OPEN C3;
					 FETCH C3 INTO M_USERID;
				CLOSE C3;
			    IF C4%ISOPEN THEN CLOSE C4; END IF;
				OPEN C4(C1_ROW.AM_PRC_MONTH);
					 FETCH C4 INTO M_ECOUNT;
				CLOSE C4;

				IF NVL(M_ECOUNT,0) = 0 THEN
                    INSERT INTO ASG_ST_AB_TIMESHEET(AT_COMP_CODE, AT_EMP_CODE, AT_EMP_NAME, AT_EDIVN_CODE, AT_EDEPT_CODE, AT_JOIN_DT, AT_LEFT_DT, AT_PERIOD,AT_USER_ID)
				    VALUES(:NEW.EMP_COMP_CODE,:NEW.EMP_CODE,:NEW.EMP_NAME,:NEW.EMP_DIVISION,:NEW.EMP_DEPT_CODE,:NEW.EMP_JOIN_DT,NULL,C1_ROW.AM_PRC_MONTH,M_USERID);
					IF C5%ISOPEN THEN CLOSE C5; END IF;
					FOR C IN C5(C1_ROW.AM_START_DT) LOOP
						M_QUERY := 'UPDATE ASG_ST_AB_TIMESHEET SET AT_D'||LTRIM(RTRIM(TO_CHAR(C.RN)))||' = ''*'''||' WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE AND AT_EMP_CODE = :NEW.EMP_CODE AND AT_PERIOD=C1_ROW.AM_PRC_MONTH AND AT_USER_ID=M_USERID';
						EXECUTE IMMEDIATE M_QUERY;					END LOOP;

                ELSE
				    IF :NEW.EMP_DIVISION != :OLD.EMP_DIVISION THEN
 				        UPDATE ASG_ST_AB_TIMESHEET SET AT_EDIVN_CODE =:NEW.EMP_DIVISION
						WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE
						AND AT_EMP_CODE = :NEW.EMP_CODE
						AND AT_EDIVN_CODE = :OLD.EMP_DIVISION
						AND AT_EDEPT_CODE = :OLD.EMP_DEPT_CODE
						AND AT_USER_ID = M_USERID;
				    END IF;
				    IF :NEW.EMP_DEPT_CODE != :OLD.EMP_DEPT_CODE THEN
 				        UPDATE ASG_ST_AB_TIMESHEET SET AT_EDEPT_CODE =:NEW.EMP_DEPT_CODE
						WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE
						AND AT_EMP_CODE = :NEW.EMP_CODE
						AND AT_EDIVN_CODE = :OLD.EMP_DIVISION
						AND AT_EDEPT_CODE = :OLD.EMP_DEPT_CODE
						AND AT_USER_ID = M_USERID;
				    END IF;
				    IF :NEW.EMP_END_OF_SERVICE_DT IS NOT NULL THEN
 				        UPDATE ASG_ST_AB_TIMESHEET SET AT_LEFT_DT =:NEW.EMP_END_OF_SERVICE_DT
						WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE
						AND AT_EMP_CODE = :NEW.EMP_CODE
						AND AT_EDIVN_CODE = :OLD.EMP_DIVISION
						AND AT_EDEPT_CODE = :OLD.EMP_DEPT_CODE
						AND AT_USER_ID = M_USERID;
				    END IF;
				END IF;
			END IF;
	    END IF;

	END IF;
END;
[/code
]
Error is comming where i have wriiten execute immediate.
Please help me out of this problem
thanx in advance.

[Updated on: Mon, 22 June 2009 07:28]

Report message to a moderator

Re: Problem in Trigger when it is updating [message #409387 is a reply to message #409383] Mon, 22 June 2009 07:29 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
The root of your problem is almost certainly the dynamic update
WHERE AT_COMP_CODE = :NEW.EMP_COMP_CODE AND 

should be
WHERE AT_COMP_CODE = ''':NEW.EMP_COMP_CODE''' AND 

etc.
Or read up on the USING clause for EXECUTE IMMEDIATE - that'd perform better.

Having said that - the logic there is highly suspect.
You seem to be equating rownum to a day of the month - that's not going to work.
Re: Problem in Trigger when it is updating [message #409394 is a reply to message #409387] Mon, 22 June 2009 07:40 Go to previous messageGo to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Thanks Dear
for giving me reply but still its not working
now it comming complation error

When i am using this
WHERE AT_COMP_CODE = ''':NEW.EMP_COMP_CODE'''
Re: Problem in Trigger when it is updating [message #409395 is a reply to message #409383] Mon, 22 June 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you post the full execute immediate line so we can see what you've done.
Re: Problem in Trigger when it is updating [message #409396 is a reply to message #409395] Mon, 22 June 2009 07:45 Go to previous messageGo to next message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
M_QUERY := 'UPDATE ASG_ST_AB_TIMESHEET SET AT_D'||LTRIM(RTRIM(TO_CHAR(C.RN)))||' = ''*'''||' WHERE AT_COMP_CODE = ''':NEW.EMP_COMP_CODE''' AND AT_EMP_CODE = ' '' :NEW.EMP_CODE'''  AND AT_PERIOD=C1_ROW.AM_PRC_MONTH AND AT_USER_ID=M_USERID';
						EXECUTE IMMEDIATE M_QUERY; 
Re: Problem in Trigger when it is updating [message #409403 is a reply to message #409383] Mon, 22 June 2009 07:56 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
This:
WHERE AT_COMP_CODE = ''':NEW.EMP_COMP_CODE''' AND 

should have been:
WHERE AT_COMP_CODE = '''||:NEW.EMP_COMP_CODE||''' AND 


You'll need to do the same for C1_ROW.AM_PRC_MONTH and M_USERID.

I still think that update isn't going to work the way expect due to your use of rownum mind.
Re: Problem in Trigger when it is updating [message #409408 is a reply to message #409403] Mon, 22 June 2009 08:16 Go to previous message
sajidrazmi
Messages: 47
Registered: August 2008
Location: oman
Member
Thank You Very much
Dear Now it's working fine.

[Updated on: Mon, 22 June 2009 08:16]

Report message to a moderator

Previous Topic: UTL_SMTP unable to send attachment
Next Topic: Mutating trigger
Goto Forum:
  


Current Time: Sun Dec 11 06:26:06 CST 2016

Total time taken to generate the page: 0.12002 seconds