p.s. Your CATCH block is in serious need of some help, as well. It doesn't even identify which line of the trigger caused a problem.
You also have mismatched datatypes for parameters (they should be NVARCHAR(128) to match the system tables/views, you hit on the INSERTED and DELETED tables way too many times just trying to figure out what type of trigger action caused the trigger to fire, and you should consider using system intrinsic functions instead of doing SELECTs from system objects to determine if something exists or not, which will inherently validate whether the input parameters are correct or not.
Also, your code is very susceptible to SQL Injection.
--Jeff Moden
Change is inevitable... Change for the better is not.