Will an MS SQL db table trigger affect the value returned by scope_identity?
Using Perl and DBI, I am attempting to execute an MS SQL database transaction that:
1. Inserts a record into table A,
2. gets the identity of the record inserted into table A,
3. inserts a record into table B with the identity from the record in table A as a value in one of the fields of the record to be inserted into table B,
4. inserts a record into table C,
5. and gets the identity of the record inserted into table C.
Steps 1 through 4 perform as expected, but step 5 doesn't return the expected record identity. Instead, zero (0) is the only value returned. If I remove steps 1 through 3 and simply attempt to insert a record into table C, the program still does not return the expected record identity. In other words, the code from steps 1 through 3 are not affecting the code in steps 4 and 5. So, I believe I have isolated the issue to table C. One important difference between table A (from which I can get the identity of the record inserted) and table C is that table A doesn't have any table triggers, while table C has five tiggers (1 insert; 4 updates).
Here is the Perl code that inserts a record into table C and attempts to get the identity of the record inserted into that table.
my $mssql = 'DECLARE @RetVal as Int; INSERT INTO TableC ( [ID], [Amount]) VALUES ( 236, 136400); Set @RetVal = SCOPE_IDENTITY(); Select @RetVal as RETURNVALUE;';
my $sth = $msdataconn->prepare($mssql);
LogMsg(7, "Executing $mssql");
my $error = $sth->execute();
my @values = $sth->fetchrow_array;
$recIDInsurance = $values[0] if defined($values[0]);
if ($msdataconn->errstr) {
LogMsg(0, $error);
LogMsg(0, $msdataconn->errstr);
# do some stuff
}
So, will an MS SQL db table trigger affect the value returned by "SCOPE_IDENTITY()"?
