>Depending on the severity of the error, SQL Server may continue executing the trigger, or it may fail the trigger/rollback all transactions on the connection and raise the error immediately. In other words, there is no true error trapping in T-SQL.
>
>It looks like you are using @@Error as a secondary test of the return value of the nested SP calls. It's much better - and faster - to test as much as you can using IF..ELSE and SELECTs rather than depending on @@ERROR. For instance, in the mm_newid procedure you could return a NULL if there is a problem generating the ID.

A NULL cannot be returned by stored procedure as return value. In case if such attemt is made, the error message "The 'xxxxxxx' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead" is generated and 0 is returned.