several layers of nested if statements 1

[lel75]

Hello,

I have the following if statement with several layers of nesting. Everything seems to work fine except for the last elseif. Am I missing something, possibly an end if somewhere?

IF (INSERTING OR ld.status_flag = 'INACTIVE') AND
new.status_flag = 'ACTIVE') THEN
INSERT INTO emp_owner.employee(emp_id, emp_name)
SELECT :new.emp_id,
:new.emp_name
FROM dual
WHERE :new.emp_id <> '9999';
ELSE
IF old.status_flag = :new.status_flag) THEN
IF ld.emp_name <>:new.emp_name THEN
UPDATE emp_owner.employee
SET emp_name = :new.emp_name
WHERE emp_id = ld.emp_id;
ELSIF :new.status_flag = 'INACTIVE' THEN
DELETE FROM emp_owner.employee WHERE emp_id = ld.emp_id;
END IF;
END IF;
END IF;

Thanks for any help you can provide - lel

 

[SantaMufasa]

Lel,

I'm not certain what logic your data causes to follow, but your visual indenting does not follow normal conventions for conditionals. I've re-aligned your code to match &quot;ELSIFs&quot; and &quot;END IFs&quot; with their associated &quot;IFs&quot;:

IF (INSERTING OR :old.status_flag = 'INACTIVE') AND
		(:new.status_flag = 'ACTIVE') THEN
	INSERT INTO emp_owner.employee(emp_id, emp_name)
		SELECT :new.emp_id, :new.emp_name
		FROM dual
		WHERE :new.emp_id <> '9999';
ELSE
	IF (:old.status_flag = :new.status_flag) THEN
		IF :old.emp_name <>:new.emp_name THEN
			UPDATE emp_owner.employee
				SET emp_name = :new.emp_name
				WHERE emp_id = :old.emp_id;
		ELSIF :new.status_flag = 'INACTIVE' THEN
			DELETE FROM emp_owner.employee
				WHERE emp_id  = :old.emp_id;
		END IF;
	END IF; 
END IF;

Does the above re-alignment reflect the logic you want? Let us know.

Dave
Sandy, Utah, USA @ 19:59 GMT, 12:59 Mountain Time

 

[lel75]

Hi Dave,

I apologize for the alignment of the code. Yes, your re-alignment reflects the logic I need. However the last elsif which deletes from the table is not working. Do you know what I am missing?

Thanks
-LeL

 

[Turkbear]

Hi,
By 'Not working' do you mean no deletion occurs or that you get an error message?

Try adding some dbms_output statements to trace the steps in the Proc to see if it ever gets to that choice.

 

[carp]

IF ld.emp_name <>:new.emp_name THEN
UPDATE emp_owner.employee
SET emp_name = :new.emp_name
WHERE emp_id = ld.emp_id;
ELSIF :new.status_flag = 'INACTIVE' THEN
DELETE FROM emp_owner.employee
WHERE emp_id = ld.emp_id;
END IF;

Are you certain that you have data where ld.emp_name = :new.emp_name AND :new.status_flag = 'INACTIVE'?
Is it possible that :new.status_flag could be something like 'Inactive' or 'inactive' or NULL?

 

[SantaMufasa]

Lel,

For your DELETE logic to execute, here is what must be true:

1) You are only UPDATING (never DELETING...see why, below in my Logic issue #2) from your trigger table (and)
2) The trigger table's ld.status_flag = 'INACTIVE' and the also :new.status_flag = 'INACTIVE' (and)
3) The trigger table's emp_name and emp_id columns have not changed.

Here, then, are my comments on your logic:
1) The above means that if you make ANY change to an &quot;INACTIVE&quot; row in the trigger table (besides changing emp_name and emp_id), it should
&quot;DELETE FROM emp_owner.employee...&quot; (Is that what you want?)
2) If you ever DELETE FROM the trigger table, it WILL NOT &quot;DELETE FROM emp_owner.employee...&quot; since one of the qualifications to execute that code is &quot;ld.emp_name = :new.emp_name&quot;. In the case of a trigger table DELETE, :new.emp_name is NULL, which can NEVER EQUAL anything.

I believe this last logic flaw is your reason for not seeing a DELETE occur against your emp_owner.employee table. Let me know if this is correct.

Dave
Sandy, Utah, USA @ 21:06 GMT, 14:06 Mountain Time

 

[lel75]

Hi Dave,

Yes, what you said is correct. For the else section below, I meant to only capture stutus_flag of 'ACTIVE' ONLY, therefore I am going to change it to old.status_flag = 'ACTIVE') AND new.status_flag = 'ACTIVE'):

ELSE
IF old.status_flag = :new.status_flag) THEN
IF ld.emp_name <>:new.emp_name THEN


I realize now looking back at the code that I want the last elsif to not be nested under the first else statement. Therefore if it doesn't match the criterias for the else above, then to go to the last else (which is the delete section). To move it out of the else condition above, would I use another ELSE IF and END IF combination?

By the way, thanks everyone for helping me figure this out...you guys are great!

 

[SantaMufasa]

Lel,

Here then is adjusted code to fit your just-previous reply:

IF (INSERTING OR ld.status_flag = 'INACTIVE') AND
new.status_flag = 'ACTIVE') THEN
INSERT INTO emp_owner.employee(emp_id, emp_name)
SELECT :new.emp_id, :new.emp_name
FROM dual
WHERE :new.emp_id <> '9999';
ELSE
IF old.status_flag = 'ACTIVE') AND new.status_flag = 'ACTIVE') THEN
IF ld.emp_name <>:new.emp_name THEN
UPDATE emp_owner.employee
SET emp_name = :new.emp_name
WHERE emp_id = ld.emp_id;
END IF;
ELSIF :new.status_flag = 'INACTIVE' THEN
DELETE FROM emp_owner.employee
WHERE emp_id = ld.emp_id;
END IF;
END IF;

My followup question, however, is, &quot;Do you ever DELETE from your trigger table?&quot; If so, then you probably want to adjust the last piece of code to read:
&quot;...ELSIF :new.status_flag = 'INACTIVE' or DELETING THEN...&quot;.

Let us know,

Dave
Sandy, Utah, USA @ 21:53 GMT, 14:53 Mountain Time

 

[lel75]

Thanks for all your help Dave. That worked like a charm. No, we do not delete from the trigger table. Thanks again.