Request review of sql validation trigger
Your code is correct as long as you plan to validate business integrity using triggers.

But I would question whether business validation in SQL Server is a right choice:


You have no means (other than parsing return error string) to process errors on client side.
This kind of code may cause deadlocks under heavy load (I can't find them here but it doesn't mean there aren't any :))
Each insert causes at least 2 table searches


Alternative way (how most applications works) is to rely on a business logic inside client application to enforce business rules. It's much easier (and straightforward) to verify that your parent is of a valid type. 

Also, based on your PostTypes table I think each type of "posts" should be different enough to store it in its own table. In that case the whole story of validating types is gone, and all the validation will be done by foreign keys.