CODEWITHSUNDEEP
sqlsql-servert-sqltriggersauditing
Animesh D
Animesh D

Reputation: 5002

Insert trigger issue

I am using a simple trigger ins_process for capturing inserts/updates on a table process. This ins_process inserts data into a table process_audit.

process has the following data originally inserted, in that order:

id    name    description
---   ----    -----------
1     proc1   sql
2     proc2   scripting
3     proc3   java

This creates the data in process_audit as follows:

id    name    description    insert_dt
---   ----    -----------    ---------
3     proc3   java           [a minute ago]
2     proc2   scripting      [a minute ago]
1     proc1   sql            [a minute ago]

Why do the records get inserted in the reverse order?. When I edit a row from the front-end, the data in process_audit is as follows:

id    name    description    insert_dt
---   ----    -----------    ---------
3     proc3   java           [a minute ago]
2     proc2   scripting      [a minute ago]
1     proc1   sql            [a minute ago]
2     proc2   scripting      [now]

This is not so much of a problem for me. I am wondering if this is a standard behaviour or if there is something wrong in the way I have created the trigger.

Here is how I created the trigger:

create trigger [dbo].[ins_process]  
on [dbo].[process] for insert, update    
as
set nocount on    
begin    
insert into process_audit
    (id, name, description, insertdate)    
select 
    id, name, description,current_timestamp
from inserted ins    
end

Also, the id column in table process_audit is not an Identity column.

Upvotes: 0

Views: 133

Answers (1)

Alex Aza
Alex Aza

Reputation: 78447

How do you know in which order SQL inserted rows to the table?

I assume you query select * from process_audit and expect to see rows in the same order as they were inserted. This is not a valid expectation.

SQL server does not guarantee any order if order by is not specified. Also, SQL server does not maintain the order of rows insertion.

Likely the returned results will be order by clustered index order, but still not guaranteed.

If you add own identity Id column or CreatedDateTime column with default constraint, this will be a better indicator of the insertion order.

Upvotes: 1

Related Questions