Why Is SQL Trigger Not Inserting Rows In Sequential Order?

Question

Recently I inherited a new ASP web application that merely allows customers to pay their outstanding invoices online. The application was poorly designed and did not have a payment history table. The entire payments table was deleted by the web service that transports the payment records to the accounting system of record.

I just created a simple trigger on the Payments table that simply copies the data from the Payments table into a Payment_Log table. Initially, the trigger just did a select * on inserted to copy the data. However, I just modified the trigger to insert the date of the payment into the Payment_Log table since one of our customers is having some issues that I need to debug. The new trigger is below. My question is that I have noticed that with this new version of the trigger, the rows are being inserted into the middle of the table (i.e. not at the end). Can someone explain why this is happening?

ALTER trigger [dbo].[PaymentHistory] on [dbo].[Payments]
for insert as

Declare @InvoiceNo nvarchar(255),
    @CustomerName nvarchar(255),
    @PaymentAmount float,
    @PaymentRefNumber nvarchar(255),
    @BulkPaid bit,
    @PaymentType nvarchar(255),
    @PaymentDate datetime

Select @InvoiceNo = InvoiceNo,
   @CustomerName = CustomerName,
   @PaymentAmount = PaymentAmount,
   @PaymentRefNumber = PaymentRefNumber,
   @BulkPaid = BulkPaid,
   @PaymentType = PaymentType
from inserted

Set @PaymentDate = GETDATE()

Insert into Payment_Log 
values (@InvoiceNo, @CustomerName, @PaymentAmount, @PaymentRefNumber, @BulkPaid, @PaymentType, @PaymentDate)

Below is a screenshot of SQL Server Management Studio that shows the rows being inserted into the middle of the table data. Thanks in advance for the help guys.

enter image description here

1) Tables don't have an order, 2) Your trigger is broken for multi-row inserts, 3) Why should the "order" be an issue anyway? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 13, 2012 at 14:43
The most important concept that you're missing here is that inserted is a table and not a row. — Yuck
– Yuck, Commented Aug 13, 2012 at 14:45

MatBailie · Accepted Answer · 2012-08-13 14:51:53Z

5

Datasets don't have an order. This means that SELECT * FROM x can return the results in a different order every time.

The only time that data is guaranteed to come back in the same order is when you specify an ORDER BY clause.

That said, there are circumstances that make the data normally come back in a certain order. The most visible one is with a clustered index.

This makes me wonder if the two tables have a Primary Key or not. Check all the indexes on each table and, at the very least, enforce a Primary Key.

As an aside, triggers in SQL Server are not fired for each row, but each batch. This can mean that the inserted table can contain more than just one row. (For example, when bulk inserting test data, or re-loading a large batch of transactions.)

For this reason, copying the data into variable is not a standard practice. Instead, you could just do the following...

ALTER trigger [dbo].[PaymentHistory] on [dbo].[Payments]
for insert as

INSERT INTO
  Payment_Log
SELECT
  InvoiceNo, CustomerName, PaymentAmount, PaymentRefNumber,
  BulkPaid,  PaymentType,  GetDate()
FROM
  inserted

answered Aug 13, 2012 at 14:51

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damien_The_Unbeliever Over a year ago

It's important to draw a distinction between primary keys and clustered indexes. The fact that one defaults to creating the other is a minor detail. And emphasise about 7 more times that, even in the presence of a clustered index, there's no guarantee on the order in which results are returned without ORDER BY.

Ian P · Accepted Answer · 2012-08-13 14:47:26Z

-1

Ok This question has the same answer as why do rows come back in different orders when I do not use an order by clause in my SQL query. If you ask SQL to process rows in any way it will process them in the fastest way it can, cashed rows first, those nearest the read head on the hard drive next and finally the rest.

Put it another way: you would be annoyed if queries took 10 times longer with rows in order than just on a first come first served basis. SQL does what you ask as quickly as it can

Hope this helps

answered Aug 13, 2012 at 14:47

Ian P

1,7241 gold badge10 silver badges12 bronze badges

2 Comments

Yuck Over a year ago

This isn't accurate information for SQL Server. It may not even be accurate in general.

Ian P Over a year ago

Well its the same answer as the one marked correct - a little bit shorter but they both say the same thing. Actually a clustered index does not ensure a return order, so in that respect the alone answer you praised is completely wong as damien pointed out.

Collectives™ on Stack Overflow

Why Is SQL Trigger Not Inserting Rows In Sequential Order?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related