SQL Server - After Insert/ For Insert - Rollback

Question

I have the below trigger:

  CREATE Trigger instructor_expertise on CourseSections
     After Insert
     As Begin
     ......

     If (Not Exists(Select AreaName From AreasOfInstructor Where (InstructorNo = @InstructorNo AND AreaName = @AreaName))) 
     Begin 
     RAISERROR('Course not in instructors expertise', 16, 1)
     rollback transaction  
     End
     GO

My question is, does 'rollback transaction' remove the row? What if it's 'For Insert' instead, does 'rollback transaction' remove the row in that case?

Thanks!!!

Answer 1

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!