This is the way that SQL Server works and I don't think that there is a way to prevent it, but you can get around it, by working with instead of trigger and not working with a trigger. If you have instead of trigger, the code inside it replaces the statement that fired the trigger, so you can check in its code whatever needs to be checked, and if everything checks O.K, you can run the statement that fired the instead of trigger. If there is a problem, you won't run the statement. This way the statement can be cancelled, but you don't need to rollback the statement.

Adi