Mike Dougherty-384281 (4/19/2013)
How does the trigger affect multi-row insert if one fails the trigger condition? How would that scenario be different with CHECK constraint?
The trigger would roll back the transaction - which is at least the entire statement (all rows), and possible the preceding statements as well if the transaction was started earlier and abort the batch.
The CHECK constraint would roll back the statement (all rows), but leave the transaction intact and continue the batch from the next statement. Of course, error handling code in the batch (or procedure, if one is used) could change that.