A huge and common mistake I have seen is when a trigger is written from the perspective that only one row is affected.

For example:

Select @someVar = SomeValue

From inserted

If the statement above is in a trigger with out some sort of looping around it and the insert/update statement affects more than one row then you only select one value from one of the many rows and skipped all of the others. SQL Server won't error out if you try to jam more than one value into the variable. (Oracle would have thrown an error)