View the order of triggers firing

We have 2 SQL Server 2008 environments with some triggers against a table containing business logic

Within the 2 environments, we can see that the triggers are firing in a different order (thanks to print statements in the triggers)

How can I VIEW the trigger firing order within each environment? I know I can use sp_settriggerorder to set them but we want to see what the differences are in the two environments

sys.sp_settriggerorder only allows you to set First or Last, nothing in between. How SQL Server will fire the middle ones (or all, if you haven't set any) is completely up to SQL Server. While in simple generalities they will probably fire in the order they were created, it may not be deterministic, and how it will fire them the next time the table is touched is not stored anywhere or predictable. Even the documentation states that the order is "undefined"...

If the order of the trigger firing matters then I strongly recommend consolidating them and not having a bunch of separate triggers in the first place. Since the behavior is undefined, even if you observed one day that triggers were firing a,b,c - nothing would prevent SQL Server from firing them c,a,b the next day. If you have used sp_settriggerorder to set first/last, you can see those manually using OBJECTPROPERTY (as also demonstrated in this answer). If you have more than three, there is no way to predict the order of the ones you haven't marked first or last.

Let's say we have this table and four triggers:

CREATE TABLE dbo.things(thing int);
GO
CREATE TRIGGER dbo.trA ON dbo.things
FOR INSERT  AS PRINT 'last';
GO
CREATE TRIGGER dbo.tr2 ON dbo.things
FOR INSERT  AS PRINT '2nd?';
GO
CREATE TRIGGER dbo.trD ON dbo.things
FOR INSERT  AS PRINT '3rd?';
GO
CREATE TRIGGER dbo.tr5 ON dbo.things
FOR INSERT AS PRINT 'first';

If we don't do anything, then an insert will fire the triggers in an arbitrary order (observation so far is order of creation, but that is not guaranteed). Example db<>fiddle results:

last
2nd?
3rd?
first

However, if we enforce two of the triggers to fire in the order we want:

EXEC sys.sp_settriggerorder @triggername = N'dbo.tr5', 
                            @order       = 'First', 
                            @stmttype    = 'INSERT';

EXEC sys.sp_settriggerorder @triggername = N'dbo.trA', 
                            @order       = 'Last',  
                            @stmttype    = 'INSERT';

Then the execution order is at least slightly more controllable. Example db<>fiddle results:

first
2nd?
3rd?
last

And we can check using OBJECTPROPERTY:

SELECT name,
  IsFirst = OBJECTPROPERTY(object_id, 'ExecIsFirstInsertTrigger'),
  IsLast  = OBJECTPROPERTY(object_id ,'ExecIsLastInsertTrigger')
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.things');

Example db<>fiddle results:

name
IsFirst
IsLast

trA
0
1

tr2
0
0

trD
0
0

tr5
1
0

If you have more than three and/or can't rely on explicit trigger ordering, the only way to know is to print output as I've done here, but I want to stress that checking this once does not mean it will execute in the same order tomorrow.

StackExchange Database Administrators Q#75464, answer score: 8