trigger's print statements show before rows affected by actual query-CodePudding

I don't have a lot of experience with triggers but I recently starting playing with them a bit. I created a trigger that looks something like this. I'm working in Azure Data Studio and I was surprised to see that my print statements and # rows affected seem to show up before the message of rows affected for the actual insert/update. Can anyone explain why this happens? Since it is an "AFTER" trigger, I would expect to see the statement for # rows updated for the initial insert/update first and then the print statements for my trigger.

create TRIGGER [dbo].[trig_trimField] ON [dbo].[mytable]
AFTER INSERT, UPDATE
AS
print '[dbo].[trig_trimField]'
IF UPDATE (myField)
BEGIN
    declare @issueCount int
    Select @issueCount = count(*) 
    from inserted
    where len(myField 'x')-1 <> len(rtrim(ltrim(myField)))
    print 'myField(s) to fix: ' cast(@issueCount as varchar)

    IF @issueCount > 0
    BEGIN
        UPDATE [dbo].[mytable]
        SET myField = rtrim(ltrim(myField))
        WHERE ID IN (
            SELECT ID
            FROM inserted
            WHERE len(myField 'x')-1 <> len(rtrim(ltrim(myField)))
            )
    END
END
GO

Test code

insert into [dbo].[mytable] ([myField])
SELECT case
            when ID = 1 then [myField]  ' '
            when ID = 2 then ' ' myField
            else myField
            end 
  FROM [dbo].[mytable]
WHERE ID in (1,2,3)

Output

[dbo].[trig_trimField]
myField(s) to fix: 2
(2 rows affected)
(3 rows affected)

CodePudding user response：

The trigger happens after the changes have been made to the target table, but in the same transaction, and before the success or failure of the DML has been reported to the client.

The trigger can throw, raiserror, or rollback to undo the change, in which case the client shouldn't see the DONE/DONEINPROC message that carries the rowcount, and should just get an error message instead.