Re: Variable has NULL in IF-statement , but does insert a valid value

"Roy Coumans" <roycoumans_at_hotmail.com> wrote in message news:480a84dd.0207220209.6f1321c4_at_posting.google.com...
> Hi,
>
> I have a problem with a trigger in Oracle 8i.
> The var 'StatusKey' is the problem. In the first UPDATE statement I
> set the LAST_STATUS_KEY to a value from a variable : 'StatusKey'. This
> variable I select in the first SELECT-Statement. Then I update table
> TRANSACTIONS with this variable. This works fine, it updates table
> TRANSACTIONS as expected. So, you might think that var StatusKey has a
> value. However, it never passes the IF-statement at the bottom. If I
> write down 'IF StatusKey IS NULL' it does go into the IF-statement.
> So, that means that the var StatusKey is NULL. How can that be since
> it correctly updates the table TRANSACTIONS with this variable?
>
> Somebody has a logical answer on this un-logical issue? Thanks, Roy
>
> CREATE OR REPLACE TRIGGER DELETE_CUSTOMER_LOG
> AFTER DELETE ON STATUSDATE
> FOR EACH ROW
> DECLARE StatusKey VARCHAR2(5);
> ThisTaKey VARCHAR2(5);
> BEGIN
> SELECT MAX(TO_NUMBER(:NEW.SD_TA_STATUS_KEY)) INTO StatusKey
> FROM SYS.DUAL;
>
> SELECT :NEW.SD_TA_KEY INTO ThisTaKey
> FROM SYS.DUAL;
>
> UPDATE TRANSACTIONS
> SET LAST_STATUS_KEY = StatusKey
> WHERE TA_KEY = ThisTaKey;
>
> IF (StatusKey = '1' ) THEN
> DELETE FROM CUSTOMER_LOG
> WHERE LOG_TA_KEY = ThisTaKey; */
> END IF;
> END;
Both selects from dual are unnecessary. They actually do nothing and you might run into type conversion problems (in the first statement you convert from varchar2 to number to varchar2. I would also recommend using the %TYPE construct when you declare variables like this statuskey statusdate.statuskey%TYPE
which makes sure the type is always correct. But anyway, you don't need these variables and you don't need the select. No reason why you can't use your columns directly. That said, AFAIK, :new variables are undefined in after delete triggers *by design*.
It also strikes me you have the */, closing a comment after your if. Try to remove it.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address