SQL Server - After Insert/ For Insert - Rollback

Your INSERT statement always runs in a transaction - either you've explicitly defined one, or if not, then SQL Server will use an implicit transaction.

You're inserting one (or multiple) row into your table. Then - still inside the transaction - the AFTER INSERT trigger runs and checks certain conditions - typically using the Inserted pseudo table available inside the trigger, which contains the rows that have been inserted.

If you call ROLLBACK TRANSACTION in your trigger, then yes - your transaction, with everything it's been doing, is rolled back and it's as if that INSERT never happened - nothing shows up in your database table.

Also: FOR INSERT is the same as AFTER INSERT in SQL Server - the trigger is executed after the INSERT statement has done its job.

One thing to keep in mind (which a lot of programmers get wrong): the trigger is fired once per statement - NOT once per row! So if you insert 20 rows at once, the trigger is fired once and the Inserted pseudo table inside the trigger contains 20 rows. You need to take that into account when writing the trigger - you're not always dealing with just a single row being inserted!