Need trigger to pull data from record right before inserted record?

[kskinne]

Using SQL 2005

I need a trigger that will run when a record is inserted in to the table, but the trigger needs to pull data from the record ID that was inserted right before the INSERTED record that initiates the trigger. I hope that makes sense. Is this possible?

Is there a way that the trigger can determine the ID of the inserted record, and if so, what would be the syntax for doing this - at that point if I had this, it wouldn't be hard to run a query against the table containing those records. If you need more info please let me know.

Thanks for your help,
Kevin

 

[markros]

Hi Kevin,

You can get what you want, but you first need to realize, that in SQL Server trigger is fired once per the statement, so, if the insert inserted multiple records, the Inserted table will contain all the records that were inserted.

Having this in mind, you can do the following

create trigger tr_Insert_Invoices AFTER INSERT for Invoices

as
 if @@ROWCOUNT = 0 -- no insert
    return

  declare @Inserts int
  select @Inserts = count(*) from Inserted
  if @Inserts > 1 -- multi-record insert
     return
  
  declare @Id int, @PrevID int
  select @Id = ID from Inserted
  select top 1 @prevID = ID from Invoices where ID < @ID order by ID DESC
  -- now we have @ID and @PrevID and can do anything we need

PluralSight Learning Library

 

[kskinne]

Hi markros thanks for your reply - the records that are inserted in my table are all part of the same transaction - however I am not sure if the program that writes the data to the database writes each individual record to the table as an individual statement, or if the records are written all at once in one statement. If it is the latter scenario, from the way it sounds this is not going to work. However I'll take what you have shared with me and test to see if I'm able to do this. Hopefully the records are written one statement at a time. I'll follow up after I've had a chance to try, or if I run into any questions. Thanks again for your help.

Kevin

 

[gmmastros]

Kevin,

If you investigate this matter and determine that the data is inserted one row at a time, and decide to move forward with this trigger approach, I would caution you. Even though the data may be inserted row by row for now, there is no guarantee that this won't change in the future.

Whenever you write a trigger, you should write it in a way that assumes multiple rows will be inserted simultaneously. If you get the trigger to work properly this way, then it will also work if only one row is inserted.

The problem is this... suppose you write the trigger with the assumption that only one row is inserted at a time, and then 6 months from now, someone changes this and you now have multiple rows inserting within a transaction. The trigger will not fail, but it won't be "doing the right thing" anymore, either. You will silently get data corruption within your database. This situation is extremely hard to detect later (once everyone forgets about the particulars of this trigger).

Trust me. This has bitten lots of people, please don't let yourself be one of them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kskinne]

Point taken, I will definitely keep this in mind - thank you

Kevin

 

[markros]

Depending on what do you need to do with the previous ID you can do

select top 1 @FirstInsertedID = ID from Inserted ORDER BY ID

and then get the prior ID.

PluralSight Learning Library

 

[kskinne]

It does appear that all the records are inserted with one SQL statement.

Thanks,
Kevin

 

[jazz1122]

Hey markros, can you please explain little more your last post.

Jazz

http://www.techno-soft.com

 

[markros]

Hi,

What exactly is not clear? Assuming you inserted many records at once and want to know the last ID before this mass insertion occurred and you have an ID column and the records are inserted in ID order, then

select top 1 @FirstInsertedID = ID from Inserted ORDER BY ID

gives you the first ID in the bulk of inserted records.

The

select top 1 @LastPreviousID = ID from TableName where ID < @FirstInsertedID ORDER BY ID DESC

will give you the maximum ID before the insert occurred.

That's all I wanted to show, but I don't know your logic and why do you need this previous ID. If you explain more about your scenario, we may be able to devise some solution.

PluralSight Learning Library