Tja, schlechtes Design, würde ich sagen.
Die Vergabe der neuen Produktnummer sollte dann am besten in eine Funktion verlegt werden, die sowohl vom Trigger als auch von deiner Prozedur aufrufbar ist.
Ansonsten sehe ich da wirklich schwarz.