Trigger Before Update OLD VALUE NULL?!?!

[DAVIDSS]

I Have a Trigger Before Insert or Update

...
elsif updating then
Temp := existencia + :OLD.CANTIDAD;
Temp := existencia - :New.CANTIDAD;
update IMS_INVENTARIO set
EXISTENCIA = Temp
WHERE ID_BODEGA = Bodega
AND ID_PRODUCTO = :NEW.ID_PRODUCTO
AND ID_CLASIFICACION = :NEW.ID_CLASIFICACION
AND ID_SUB_CLASIFICACION = :NEW.ID_SUB_CLASIFICACION;
end if;
...

when I update the table, the line "Temp := existencia + :OLD.CANTIDAD " seems like it doesn't work, I guess that :OLD.CANTIDAD doesn't have a value...

What's goin on???

 

[slaing]

How can you tell what 'Temp := existencia + :OLD.CANTIDAD;' is doing ? - you immediately overwrite it with another calculation.

However, in Oracle , x + NULL = NULL , so yes, if :OLD.CANTIDAD is NULL then the result of your calculation will be null.

I'd either NVL it : ie
'Temp := existencia + NVLOLD.CANTIDAD,0);'
(and the same for the next calc too ,but should it be
Temp := Temp - :New.CANTIDAD ?)
or else change the column definiton to be NOT NULL DEFAULT 0;
(assuming Temp, existencia and CANTIDAD are all numeric)

HTH

Steve

 

[DAVIDSS]

Thanks for the help, sorry I wrote that wrong, indeed is like you say, but the NVL what exactly does? makes the null value to 0?

David ....

 

[slaing]

Yes, NVL(my_var, 0) will return 0 if my_var is null

NVL is extremely useful in avoiding calculations collapsing through one of the elements being null - it can also be used with dates, strings.

Steve