时间戳

首页 > 解决方案 > Perl, SQL Server use of @@IDENTITY

sql-server - Perl, SQL Server use of @@IDENTITY

问题描述

I have a SQL Server table with an identity column, set to autoincrement.

Coded in Perl, the insert in the code below works fine, in the while loop the fetchrow_array() call returns no data in the @row array.

How do I best retrieve the identity value for use in subsequent SQL statements?

my $term_sql = "INSERT INTO reminder_term(site, name, description, localization) OUTPUT \@\@IDENTITY VALUES(?,?,?,?)";
my $t_stmt = $dbh->prepare($term_sql);
...
$t_stmt->execute($site, $name, $description, $localizer);
 
while (@row = $t_stmt->fetchrow_array()) {
    $referential_key = $row[0];
}

标签： sql-serverperl

解决方案

避免使用 @@IDENTITY 值，因为它在存在触发器时不可靠。

给定示例表架构...

create table [dbo].[reminder_term] (
  [id] int not null identity(1,1),
  [site] nvarchar(10),
  [name] nvarchar(10),
  [description] nvarchar(10),
  [localization] nvarchar(10)
);

您可以稍微修改 OUTPUT 子句，您可以id通过特殊inserted行源捕获新值...

INSERT INTO reminder_term(site, name, description, localization)
OUTPUT inserted.id
VALUES(?,?,?,?)

推荐阅读