Un trigger que dispara un procedimiento que dispara un trigger...

[[Lepe]]

Que en un trigger after insert ejecute un SP para agregar un registro a otra tabla, lo veo correcto.

Que ese SP modifique la primera tabla, eso ya no me parece lógico. Quizás deberías usar un trigger before Insert, así modificas campos en la primera tabla, y además lanzas la inserción en la segunda.

El SP se supone está compilado... pero no me queda claro eso de hacer 2 inner joins cuando se va a ejecutar en un trigger, quizás fuera mejor usar una vista.
Para este detalle me gustaría que otros foristas opinaran.

Si ya tienes un trigger before insert de la primera tabla y no quieres modificarlo, siempre puedes usar el "position 1"

Saludos