A different number of AFTER INSERT trigger alarms in SQLite and SQL Server
In SQL, triggers can be fired "per row" or "per statement".

SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing).

Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which are specified by the phrases FOR EACH ROW or FOR EACH STATEMENT in the trigger's definition (though amusingly both have different defaults: DB2 defaults to per-row, PostgreSQL to per-statement). I vaguely recall Oracle supports both types too but there was something weird about its "per statement" variant.

Typically per-row triggers have access to the before/after state of the row they are triggered by, while per-statement triggers have access to a pseudo-table of the rows that the triggering statement affects.