SQL & PL/SQL

So im trying to get a trigger to work with an alert and the Alert seems to be right and the trigger complies which seems right to me, however the instruction that I have in my book does not produce the same output that I get from my Update.

Here is the deal. I am to log into sql * with a default account as well as login as "SYSTEM"
the trigger should invoke the Alert and output a message to re-order some more product and the status should = 0 since there is no wait time. However I don't get a "Message" from the Alert and the status = 1 which indicates timeout. So if you can take a look at my code and let me know what I did wrong or how to "Connect" the two that would be great.


Trigger I created.

CREATE OR REPLACE TRIGGER order_replace_trg
AFtER UPDATE OF stock on bb_product
FOR EACH ROW
WHEN (OLD.stock = 24 AND NEW.stock = -2)

DECLARE

stock NUMBER(5,1);
idproduct NUMBER(2);
lv_msg_txt VARCHAR2(25);
lv_status_num NUMBER(1);
reorder NUMBER(3);


BEGIN



IF stock <> 24 AND reorder = 25 THEN
lv_msg_txt := 'Product 4 Reorder Time!';
DBMS_OUTPUT.PUT_LINE(lv_msg_txt);
ELSE
lv_status_num := 0;
DBMS_OUTPUT.PUT_LINE(lv_status_num);
END IF;

END;
/




The Alert:

BEGIN
DBMS_ALERT.REGISTER('reorder');
END;
/



DECLARE
lv_msg_txt VARCHAR2(25);
lv_status_num NUMBER(1);
BEGIN
DBMS_ALERT.WAITONE('reorder', lv_msg_txt, lv_status_num, 120);
DBMS_OUTPUT.PUT_LINE('Alert: ' ||lv_msg_txt);
DBMS_OUTPUT.PUT_LINE('Status: ' ||lv_status_num);
END;
/



Here is the block I need to run to test the trigger and alert.

UPDATE bb_product
SET stock = stock -2
WHERE idproduct = 4;
COMMIT;


The message I should get is:

Alert: Product 4 Reorder Time!
Status: 0

PL/SQL procedure successfully completed.

This is what I get.

SQL> /
Alert:
Status: 1

PL/SQL procedure successfully completed.





Thanks for your help!

Mac