T-SQL and Transaction Flow - First to Last

Question

Let's say I have a TabA table with columns:

• col1 - primary key (but not identifier)

• col2 - foreign key

• col3 - with unique constraint

• col4 - with control limit

• col5 - with NOT NULL constraint

In addition, TabA has 2 triggers:

• INSTEAD OF INSERT - this is the one to insert into TabA (of course), but in it own code inserts a new line into TabA . Values ​​for all columns in this new row are guaranteed to be correct.

• AFTER INSERT - it's just printing a line

Now I am ready to insert a new line into TabA (INSERT INTO TabA VALUES (...)). Obviously, we should expect some events:

• the value for col1 must be checked for uniqueness and NOT NULL (primary key)

• the value for col2 must be checked against the parent table (foreign key)

• Col3 value must be checked for uniqueness

• Value for col4 should be checked for check constraint

• value for col5 should be checked for NOT NULL

• INSTEAD OF trigger must be executed

• AFTER trigger trigger

I want to reorder this list (1-7) so that the number 1 is in the event that will happen first, 2 = the event that will happen second, ... and 7 for the last event.

In addition, if event X causes an error (for example, col5 = NULL) - does this mean that events X + 1, X + 2 .. will NOT ?

Thanks for the help!

Answer 1

This can be easily verified by setting up test patterns as described in the print statements in triggers, and simply trying to insert invalid values. It gave me

• Instead of a trigger
• Checks NULL PK
• Checks for NULL column 5
• Verifies the uniqueness of PK restrictions
• Verifies the uniqueness of a unique constraint
• Checks column check constraint 4
• Checks FK Constraint
• Fires after a trigger

As far as I know, the order of 1.7 and 8 is guaranteed. The rest are arbitrary. Any error will stop the next steps.

Answer 2

The general execution order for the INSERT, UPDATE, DELETE statements:

• Provide all table and row level constraints. Note that you have zero control over the order in which these restrictions are noted.
• If an INSTEAD OF trigger exists for the statement being executed, execute it.
• Execute all relevant AFTER triggers in undefined order with the following exceptions:
  • If the first or last one to be executed via sp_settriggerorder was specified after the trigger, execute them at the corresponding point with any remaining triggers executed in undefined order.

You can only have 1 INSTEAD OF trigger (per action: INSERT, UPDATE or DELETE). This trigger will always be executed before the AFTER trigger is triggered, because it is executed instead of the corresponding INSERT, UPDATE, or DELETE statements.

AFTER the triggers are always executed, oddly enough, AFTER the execution of the data modification instruction.

NOTE. If you have an INSTEAD OF trigger, I don’t understand without spending any real time on INSTEAD OF triggers, regardless of whether table / row constraints are enforced to trigger the INSTEAD OF trigger. This is something you can experiment with.

Answer 3

This can be easily verified by setting up test patterns as described in printing statements in triggers and simply trying to insert invalid values.

Of course I did it! And agree with you - get the same result from 1 to 8. And for me, this is embarrassment - a quote from the book "Microsoft SQL Server 2008 Bible" (Paul Nielsen). That is (on page 637):

Each transaction moves through various checks and code in the following order:

• INSERT IDENTIFICATION
• Limitation of uncertainty
• Data Type Check
• INSTEAD OF Trigger Trigger. If an INSTEAD OF trigger exists, then DML execution stops here. INSTEAD OF triggers are not recursive. Therefore, if an INSERT trigger executes another DML command, then the INSTEAD OF trigger will be ignored a second time (recursive triggers are discussed later in this chapter).
• Primary key restriction
• Check restrictions
• Foreign key constraint
• Running and updating DML in the transaction log
• AFTER trigger trigger
• Commit transaction

So, as you can see, this IT-Pro does not agree with you and me. For example, he gives the trigger number INSTEAD OF 4, and we give him the number 1. This quote is just confusing to me!