I have a PHP application using a sql-server database and trigger. The workflow is the following:
- I create an object in the DB,
- a trigger logs the creation into a dedicated table, by inserting a row within,
- then from PHP I get the last inserted ID.
我有一个使用sql-server数据库和触发器的PHP应用程序。工作流程如下: - 我在数据库中创建一个对象, - 触发器通过在其中插入一行将创建记录到专用表中,然后从PHP获取最后插入的ID。
The problem is that the last inserted ID is related to the log row and not to my object.
问题是最后插入的ID与日志行有关,而与我的对象无关。
From the PHP code, I cannot do in another way as this is done by my ORM (Propel). So I must make it work on the DB side. Is there any way to achieve it, eg by telling the trigger not to be considered as a "last inserted id' ?
从PHP代码,我不能以另一种方式做,因为这是由我的ORM(Propel)完成的。所以我必须让它在数据库方面工作。有没有办法实现它,例如通过告诉触发器不被视为“最后插入的id”?
3 个解决方案
#1
You need to use SELECT SCOPE_IDENTITY() in your stored procedure to get the last ID created in the current session, scope.
您需要在存储过程中使用SELECT SCOPE_IDENTITY()来获取在当前会话范围中创建的最后一个ID。
if you use another option, it will return the ID of the Log table.
如果您使用其他选项,它将返回Log表的ID。
#2
You need to use Scope_Identity(). This gets the last ID generated in the current session and scope (triggers run in their own scope).
您需要使用Scope_Identity()。这将获取当前会话和范围中生成的最后一个ID(触发器在其自己的范围内运行)。
#3
You have several options: @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT
您有几种选择:@@ IDENTITY vs SCOPE_IDENTITY()vs IDENT_CURRENT
-
SELECT @@IDENTITYIt returns the last
IDENTITYvalue produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.它返回在连接上生成的最后一个IDENTITY值,无论生成该值的表如何,也不管生成该值的语句的范围如何。
-
SELECT SCOPE_IDENTITY()It returns the last
IDENTITYvalue produced on a connection and by a statement in the same scope, regardless of the table that produced the value.它返回在连接上生成的最后一个IDENTITY值以及同一范围内的语句,而不管生成该值的表。
-
SELECT IDENT_CURRENT(‘tablename’)It returns the last
IDENTITYvalue produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.它返回表中生成的最后一个IDENTITY值,无论创建该值的连接如何,也不管生成该值的语句的范围如何。
IDENT_CURRENTis not limited by scope and session; it is limited to a specified table.IDENT_CURRENTreturns the identity value generated for a specific table in any session and any scope.IDENT_CURRENT不受范围和会话的限制;它仅限于指定的表格。 IDENT_CURRENT返回在任何会话和任何范围内为特定表生成的标识值。
#1
You need to use SELECT SCOPE_IDENTITY() in your stored procedure to get the last ID created in the current session, scope.
您需要在存储过程中使用SELECT SCOPE_IDENTITY()来获取在当前会话范围中创建的最后一个ID。
if you use another option, it will return the ID of the Log table.
如果您使用其他选项,它将返回Log表的ID。
#2
You need to use Scope_Identity(). This gets the last ID generated in the current session and scope (triggers run in their own scope).
您需要使用Scope_Identity()。这将获取当前会话和范围中生成的最后一个ID(触发器在其自己的范围内运行)。
#3
You have several options: @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT
您有几种选择:@@ IDENTITY vs SCOPE_IDENTITY()vs IDENT_CURRENT
-
SELECT @@IDENTITYIt returns the last
IDENTITYvalue produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.它返回在连接上生成的最后一个IDENTITY值,无论生成该值的表如何,也不管生成该值的语句的范围如何。
-
SELECT SCOPE_IDENTITY()It returns the last
IDENTITYvalue produced on a connection and by a statement in the same scope, regardless of the table that produced the value.它返回在连接上生成的最后一个IDENTITY值以及同一范围内的语句,而不管生成该值的表。
-
SELECT IDENT_CURRENT(‘tablename’)It returns the last
IDENTITYvalue produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.它返回表中生成的最后一个IDENTITY值,无论创建该值的连接如何,也不管生成该值的语句的范围如何。
IDENT_CURRENTis not limited by scope and session; it is limited to a specified table.IDENT_CURRENTreturns the identity value generated for a specific table in any session and any scope.IDENT_CURRENT不受范围和会话的限制;它仅限于指定的表格。 IDENT_CURRENT返回在任何会话和任何范围内为特定表生成的标识值。