Set the Priority for two different "after insert" triggers in SQL Server

I'm trying to create triggers by functionality,instead of DML operation.

For exp,I have 2 triggers A and B. A is for "AFTER insert, update , DELETE" , and B is for "AFTER INSERT". how can I know which one(A or B) is ran first after insert a new record?

You can specify the first and/or last trigger to fire using sys.sp_settriggerorder. If you have two or three triggers, this allows you to completely control the execution order of all triggers. If you have more than three, you can make one first, another fire last, but the order of execution for the triggers in the middle will be non-deterministic.

In your case, to make sure A always fires first:

EXEC sys.sp_settriggerorder N'dbo.A', 'First', 'INSERT';

To make sure B always fires last (while not necessary when there are only two triggers, you could add more later, just note that if you later attempt to specify another trigger as last, it will generate an error):

EXEC sys.sp_settriggerorder N'dbo.B', 'Last', 'INSERT';

If you're not asking how to control the order but rather to determine after the fact which one fired first for a specific insert, then you will have to add logging to both triggers. Once you are logging both triggers' activity to a table, you can determine order by an identity column on the logging table, or a date/time column populated with the current time.

Of course, you are probably better off combining the two triggers into one (and the part that is insert only would have conditional logic around it). I did not test the overhead of additional insert logic in an insert/update/delete trigger getting bypassed when invoked by a delete or update operation, but in general, my tests concluded that a single trigger has less overhead than multiple even when the end result affected the same number of rows:

• T-SQL Tuesday #64 : One Trigger or Many?

StackExchange Database Administrators Q#128217, answer score: 4