Certain Extent

When are triggers fired, and how many times?

For your standard missionary position trigger, they are fired once per batch. So if you have an update statement that affects 57 rows, then the DML AFTER trigger fires *once* but the deleted and inserted magic tables have 57 rows in them. One common anti-pattern is not handling multiple values in the inserted/deleted tables.  Code to prove the firing behavior of triggers:

-- Test trigger firing cardinality
if object_id('TestTriggerFiring', N'U') > 0
    drop table TestTriggerFiring

if object_id('TestTriggerAudit', N'U') > 0
    drop table TestTriggerAudit

if object_id('tr_TestTriggerFiring', N'TR') > 0
    drop trigger dbo.tr_TestTriggerFiring

create table TestTriggerFiring
(
    KeyID int primary key clustered
    , Payload varchar(max) NOT NULL
    , LastUpdatedDate datetime NULL
    , LastUpdatedBy sysname NULL
)

create table TestTriggerAudit
(
    TriggerName sysname
    , DateFired datetime NOT NULL
    , ActionType varchar(10) NOT NULL
    , KeyID int NOT NULL
    , Stamp uniqueidentifier
)

go

create trigger dbo.tr_TestTriggerFiring on dbo.TestTriggerFiring
for insert, update
as

declare @MyGuid uniqueidentifier
set @MyGuid = newid()

insert into TestTriggerAudit (TriggerName, DateFired, ActionType, KeyID, Stamp)
select 'tr_TestTriggerFiring', getdate(), 'inserted', i.KeyID, @MyGuid
from INSERTED i

go

insert into TestTriggerFiring
select top 3 object_id, name, null, null
from sys.objects

go

insert into TestTriggerFiring values (-1, 'test4', null, null)
waitfor delay '00:00:001'
go
insert into TestTriggerFiring values (-2, 'test5', null, null)
waitfor delay '00:00:001'
go
insert into TestTriggerFiring values (-3, 'test6', null, null)
waitfor delay '00:00:001'
go

select * from TestTriggerAudit

What are the inserted/deleted magic tables? How do they work?

The inserted and deleted tables hold the date being changed. Depending on what you are doing they hold different data:

Insert
Inserted – holds the new data
Deleted – is empty

Update
Inserted – holds new data
Deleted – holds old data

Delete
Deleted – data being deleted
Inserted – is empty

The inserted and deleted tables are not indexed, so take care in querying them in the wrong way.

Given the logic above, if possible it is normally cleaner to not combine an insert/update trigger. If you have to, the below if my template for doing so:

IF OBJECT_ID('tr_Example_Update', 'TR') > 0
    DROP TRIGGER dbo.tr_Example_Update
GO

CREATE TRIGGER dbo.tr_Example_Update ON dbo.Test
FOR UPDATE, INSERT
AS 

IF @@ROWCOUNT = 0
RETURN

SET NOCOUNT ON
IF UPDATE(Field1) OR UPDATE(Field2)
BEGIN
    -- Update
    IF (SELECT COUNT(1) FROM DELETED) > 0
    BEGIN
        print 'Update logic'
    END
    ELSE
    BEGIN
        PRINT 'Insert logic'
    END
END

SET NOCOUNT OFF
GO

Are triggers fired on bulk insert?
No by default, but you can turn them on. Controlling Trigger Execution When Bulk Importing Data

Tune in for Triggers Part 3: FAQ and FOP continued

sqlserver