Results 1 to 3 of 3

Thread: Will an MS SQL db table trigger affect the value returned by scope_identity?

  1. #1
    Join Date
    Dec 2011
    Posts
    2

    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()"?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Trigger should not impact SCOPE_IDENTITY, did the record get inserted into C?

  3. #3
    Join Date
    Dec 2011
    Posts
    2
    I've actually found the solution to this issue.

    The record did get inserted into table C. It looks like the issue is with the Perl module I am using but it is related to the existence of triggers on the table. In other words, the MS SQL DB is doing its job, but when the information is returned, the Perl Module doesn't properly handle the information returned from the triggers on the table that fire.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •