Se ejecutan cuando borras, insertas o actualizas un registro de la tabla a la que le creaste el trigger.
Aquí está una parte de la documentación de SQL Server:
Cita: A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.
A CREATE PROCEDURE or CREATE TRIGGER statement cannot span batches. This means that a stored procedure or trigger is always created in a single batch and compiled into an execution plan.