1

I have the same application on different hosts bulk inserting into the same table. Each bulk insert fires a trigger. The structure of the table is as follows:

Hostname    Quantity
---------------------
BOX_1       10
BOX_1       15
BOX_1       20
BOX_1       11

If I have the following code as part of the trigger:

DECLARE @hostname VARCHAR(20)
SELECT @hostname = Hostname
FROM INSERTED

Each bulk insert contains only one hostname since the application is only capturing data from the box its running on, but if two machines bulk insert simultaneously into the same table, could the INSERTED table be a combination of bulk inserts from different machines?

Or will the triggers execute sequentially, meaning the INSERTED table will always contain data from only one application at a time?

I need to know if my code setting the @hostname variable has any possibility of not being confined to just one choice.

eek
  • 688
  • 2
  • 21
  • 32
  • 1
    No, you won't get one invocation of a trigger from two separate processes trying to insert. – pmbAustin Oct 20 '15 at 15:05
  • This is a poor design,. You should never be using a scalar variable as a value from inserted as the inserted table can contain multiple records. This could be the cause of any issues you might have now or in the future..You truly need to fix this as it is an unacceptable technique to use in a SQL server trigger. – HLGEM Oct 20 '15 at 15:31
  • Normally true, HLGEM, but in this case there can only ever be a single value so it's more-or-less okay. Though the assignment should do a SELECT TOP 1 or SELECT DISTINCT or something to indicate that the code "knows" that there will only be one value (and possibly be more efficient) – pmbAustin Oct 20 '15 at 16:23

1 Answers1

1

The INSERTED (and DELETED) table will only ever contain rows from the statement that caused the trigger to fire.

See here: https://msdn.microsoft.com/en-us/library/ms191300(v=sql.110).aspx

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

The rows in these tables are effectively scoped to the insert/update/delete statement that caused the trigger to fire initially.

See also here for some more info: SQL Server Trigger Isolation / Scope Documentation

But bear in mind in your trigger design that some other insert or update operation (a manual bulk insert, or data maintenance) might cause your trigger to fire, and the assumption about the hostname may no longer hold. Probably this will be years down the line after you've moved on or forgotten about this trigger!

Community
  • 1
  • 1
beercohol
  • 2,577
  • 13
  • 26