Sql-server – Trigger in combination with transaction

Your trigger is fired for each statement not for each row. In statement level triggers you can not access the new and old records.

You need to change your create trigger to create a row-level trigger, rather than a statement level trigger:

CREATE TRIGGER my_trigger
  AFTER INSERT ON table2
  FOR EACH ROW -- this is the change
EXECUTE PROCEDURE my_procedure();

Triggers are always executed in the same transaction as the triggering statement.

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done.

In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update).

Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, if the INSTEAD OF trigger did nothing, there can't be anything for an AFTER trigger to do either, right?

Note you can also get some very confusing behaviours if the server configuration option nested triggers (shown as Allow Triggers to Fire Others in the SSMS Advanced Server Properties Tab) is set to 1 (the default).

From that link:

The nested triggers option controls whether an AFTER trigger can cascade. That is, perform an action that initiates another trigger, which initiates another trigger, and so on. When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.