Transaction Time Tables in MSSQL and Constraints
The trigger is pretty slow on larger tables.

You don't have any indexes, and you aren't limiting the check to the rows affected by the statement using the INSERTED virtual table.  See: Use the inserted and deleted tables

many operations like updates require more than one operation

In scenarios like this you're better-off managing the updates through a stored procedure, rather than trying to handle everything with a trigger.  For instance if you insert an adjoining interval, you'd probably want to coalesce them.