如何使用PHP返回安装了触发器的MSSQL表上最后插入的ID

Question

我试图在MSSQL数据库上运行查询，并使用PHP返回插入ID。在此之前，我一直使用下面的代码，它工作得很好:

 $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 );

但是，由于集成需求，我们向Pricing表添加了一个触发器，每当运行INSERT查询时，该触发器将数据复制到另一个数据库中的临时表中。这就改变了操作的顺序。据我所知，发生了以下情况:

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

我之前一直在使用SCOPE_IDENTITY，所以触发器覆盖最后一个插入ID是有意义的。但当我弄明白这一点后，我切换到IDENT_CURRENT('Pricing')，以便在我的请求中更明确。但是，它仍然返回一个空值。

我应该补充一下，如果我们禁用触发器，这段代码可以完美地工作。

是否有任何方法返回上有触发器的表上最后插入的ID ?

非常感谢你的建议和想法。

编辑:触发点:

 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

编辑:

解决!

嗯，我仍然不明白为什么，但问题似乎是由使用触发器和试图将查询组合在一起的奇怪组合引起的。显然，如果表上有一个触发器，则需要单独运行查询。下面是最终成功的方法:

 $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'];

Answer 1

Try

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

IDENT_CURRENT

这是另一种"口味"…

IDENT_CURRENT('MyTableName')

编辑 ---------

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

Answer 2

我已经很长时间没有使用MSSQL了，但我曾经使用过这个…

SELECT LAST_INSERT_ID=@@IDENTITY

还能用吗?