How to use PHP to return the last inserted ID on an MSSQL table with Triggers installed

Question

I am attempting to run a query on an MSSQL database and return the insert ID using PHP. Previously, I had been using the following code for this, which worked fine:

 $q = "INSERT INTO Pricing (Products_idProducts,FromQty,ToQty,Price) VALUES((?),(?),(?),(?)); SELECT IDENT_CURRENT('Pricing') AS 'id';";
 $params = array( $_POST['idProduct'], $_POST['Pricing-FromQty'], $_POST['Pricing-ToQty'], $_POST['Pricing-Price'] );
 $r = sqlsrv_query( $db, $q, $params );
 sqlsrv_next_result( $r );
 sqlsrv_fetch( $r );
 $LastID = sqlsrv_get_field( $r, 0 );

However, due to integration requirements, we added a trigger to the Pricing table that copies the data to a temporary table in another database whenever an INSERT query is run. This kind of changes the order of operations. From what I can tell, the following is occurring:

 Web App -> INSERT into Pricing Table
 Trigger -> INSERT into Temp Table
 Web App -> Get ID

I had previously been using SCOPE_IDENTITY, so it made sense that the trigger was overwriting the last insert ID. But when I figured this out, I switched to IDENT_CURRENT('Pricing') to be more explicit in my request. However, it is still returning a null value.

I should add that if we disable the trigger, this code works perfectly.

Is there any way to return the last inserted ID on a table that has triggers on it?

Thanks so much for any advice or ideas.

EDIT: Here is the Trigger:

 IF EXISTS (SELECT 1 FROM inserted cp)
 BEGIN
      INSERT INTO [ProductManager].._PRICESTAGING ([ITEMNUM],[QFR],[QTO],[PRICE])
      SELECT PartNumber,[FromQty],[ToQty],Price   
      FROM inserted cp
 END

EDIT: Solved!

Well, I still don't understand exactly why, but the problem seems to have been caused by a weird combination of both using a trigger, and trying to combine the queries together. Apparently, if a table has a trigger on it, you need to run queries separately. Here is what eventually worked:

 $q = "INSERT INTO Pricing (Products_idProducts,FromQty,ToQty,Price) VALUES((?),(?),(?),(?));";
 $params = array( $_POST['idProduct'], $_POST['Pricing-FromQty'], $_POST['Pricing-ToQty'], $_POST['Pricing-Price'] );
 $r = sqlsrv_query( $db, $q, $params );

 // bypass trigger nonsense
 $q = "SELECT IDENT_CURRENT('Pricing') AS 'id';";
 $r = sqlsrv_query( $db, $q );
 $LastID = sqlsrv_fetch_array( $r );
 $LastID = $LastID['id'];

I'm upping the question...because that's a good tidbit for future readers about the separate lines. — granadaCoder, May 22 '13 at 21:25

granadaCoder · Accepted Answer · 2013-05-22T19:09:02.403

Try

http://msdn.microsoft.com/en-us/library/ms175098.aspx

IDENT_CURRENT

It's another one of the "flavors"...

IDENT_CURRENT( 'MyTableName' )

EDIT---------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vegetable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[Vegetable]
    END
GO

CREATE TABLE [dbo].[Vegetable] (
      VegetableKey          int IDENTITY (1001 , 1 ) not null
    , VegetableName     varchar(64) not null
) 
GO


ALTER TABLE dbo.Vegetable ADD CONSTRAINT PK_Vegetable_VegetableKey
PRIMARY KEY CLUSTERED (VegetableKey)
GO


ALTER TABLE dbo.Vegetable ADD CONSTRAINT CK_Vegetable_VegetableName_UNIQUE 
UNIQUE (VegetableName)
GO


GRANT SELECT , INSERT, UPDATE, DELETE ON [dbo].[Vegetable] TO public
GO








if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FoodItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        DROP TABLE [dbo].[FoodItem]
    END
GO

CREATE TABLE [dbo].[FoodItem] (
      FoodItemKey           int IDENTITY (2001 , 1 ) not null
    , FoodItemName      varchar(64) not null
) 
GO


ALTER TABLE dbo.FoodItem ADD CONSTRAINT PK_FoodItem_FoodItemKey
PRIMARY KEY CLUSTERED (FoodItemKey)
GO


ALTER TABLE dbo.FoodItem ADD CONSTRAINT CK_FoodItem_FoodItemName_UNIQUE 
UNIQUE (FoodItemName)
GO


GRANT SELECT , INSERT, UPDATE, DELETE ON [dbo].[FoodItem] TO public
GO










CREATE TRIGGER CopyVegetableToFoodItemTrigger on dbo.Vegetable
FOR INSERT , UPDATE AS  

INSERT into dbo.FoodItem (FoodItemName)
Select i.VegetableName from
 inserted i
 where not exists ( select null from dbo.FoodItem innerRealTable where innerRealTable.FoodItemName = i.VegetableName )

GO


declare @MyIdentity int

INSERT INTO dbo.Vegetable ( VegetableName ) select 'Pumpkin'
select @MyIdentity = IDENT_CURRENT( '[dbo].[Vegetable]' )
print @MyIdentity


INSERT INTO dbo.Vegetable ( VegetableName ) select 'Tomato'
select @MyIdentity = IDENT_CURRENT( N'[dbo].[Vegetable]' )
print @MyIdentity


select * from dbo.Vegetable

select * from dbo.FoodItem

Yes, you'll see in my example code that I am currently using IDENT_CURRENT('Pricing'). It returns nothing, when the trigger is active on that table. Is there any alternative method? Thanks. — Typel, May 22 '13 at 18:42
That is weird, because the big deal with this function was that it wasn't affected by triggers. See the demo code here: http://msdn.microsoft.com/en-us/library/aa933217(v=sql.80).aspx — granadaCoder, May 22 '13 at 18:44
Yes, very strange. This morning I actually tested it without the trigger enabled again just to be certain, and it worked perfect. Then as soon as I enabled the trigger, it stops functioning. Maybe there's something in the trigger that's messing up ident_current? Is that even possible? Hmm. — Typel, May 22 '13 at 18:50
Thanks. That's a good idea. It doesn't seem to have fixed it in this case, though. I tried 2 explicit names: [ProductManager].[dbo].[Pricing] and [dbo].[Pricing] I'm really starting to lean towards the idea that there's something happening in that trigger. I didn't actually write the trigger, but I'm going to spend some time reviewing that code and see if there's anything weird going on. — Typel, May 22 '13 at 19:01
Posted it. I'm not sure if it is very helpful. It looks like just a simple insert copy trigger. — Typel, May 22 '13 at 19:23

score 0 · Answer 2 · answered May 22 '13 at 18:37

0

It's a very long time since I had to work with MSSQL, but I used to use this...

SELECT LAST_INSERT_ID=@@IDENTITY

Does that still work?

answered May 22 '13 at 18:37

WalterEgo

515
6
20

Unfortunately, I've tried this as well and it has the same issue. SCOPE_IDENTITY, @@IDENTITY, and IDENT_CURRENT('tablename') all come up empty handed when that trigger is active. I was hoping there was some alternative way to get the id. Thanks, though. – Typel May 22 '13 at 18:40

How to use PHP to return the last inserted ID on an MSSQL table with Triggers installed

2 Answers2