Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: blindman stumped by trigger and identity issue.

    The blindman is totally stumped!

    I've run into a situation that looks absolutely crazy to me. Makes no sense at all.

    We're trying to replicate some data tables, which means they must have primary keys. Two tables do not have natural key candidates. They contain duplicate records. So I added an identity column to the end of each table.
    These tables each have triggers on them for creating records in matching audit tables. I modified the triggers to enumerate the columns, and the one of the tables works fine. The second table does not. The trigger fires and populates the audit table for manual edits, but attempting to inserts record through the application does not work. The application fails to insert records to either the primary or the audit table.

    So to debug, I backed out a ways, and then started adding bits and pieces while continuous testing:

    Overview:
    The application inserts to table A.
    A trigger on table A inserts to table B.

    Tests:
    Added nullable int column "ID" to table B.
    ---Manual inserts to table A work. Audit records are created in table B.
    ---Application inserts to table A work. Audit records are created in table B.

    Changed ID to non-nullable, default 1.
    ---Manual inserts to table A work. Audit records are created in table B.
    ---Application inserts to table A work. Audit records are created in table B.

    Changed ID to identity column.
    ---Manual inserts to table A work. Audit records are created in table B.
    ---Application fails to insert to either table A or table B.

    Changed ID to GUID, default newid().
    ---Manual inserts to table A work. Audit records are created in table B.
    ---Application inserts to table A work. Audit records are created in table B.

    So what gives? What could possibly cause an application to prevent an identity value from being generated in a table that it does not touch, on a column that it knows nothing about?

    Any suggestions?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the application inserting via a stored procedure, or is it doing direct SQL on the table? If the application does not know about the new identity column, could it be failing on the insert due to "too many or too few columns in insert statement" type of error?

    how about if you run a trace on the server, to monitor for errors, and see what that gives you?

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Have you populated all the rows with new identity values?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The application is inserting on its own, not through a stored procedure. But it is inserting only to table A. Table is B is populated by the trigger on table A. The trigger is apparently failing, related somehow to the existence of the identity column and only when the trigger is fired by the application, which I assume causes the insert to table A to fail and get rolled back.

    Yes, the rows are populated with identity values. That happens when the identity column is created.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe something to do with connection settings? Though the whole setup worked fine on the other table...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Could the application be sophisticated enough to start a transaction? If so, any failure in the trigger could be picked up as an error on the insert, and potentially rolled back. Except that the trigger works if you do not use the application. Try doing the insert into table a with SET QUOTED_IDENTIFIER OFF. That seems to be the most common culprit in such situations. The MS tools have that on by default.

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    Yes, the rows are populated with identity values. That happens when the identity column is created.
    Couldn't remember or not so I had to check.

    _______________

    create table ckyIndentify (iName varchar(10), iFieldTwo int)

    declare @inkymentor int
    set @inkymentor = 1

    while @inkymentor < 100
    begin
    insert into ckyIndentify (iName,IFieldTwo)
    select 'John ' + convert(varchar(5),@inkymentor),@inkymentor
    set @inkymentor = @inkymentor + 1
    end


    ALTER TABLE dbo.ckyIndentify ADD

    -- Add a PRIMARY KEY identity column.
    Id INT IDENTITY
    CONSTRAINT Id_pk PRIMARY KEY

    select * from ckyIndentify

    drop table ckyIndentify

    _______________

    Yep, you're correct.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    Try doing the insert into table a with SET QUOTED_IDENTIFIER OFF. That seems to be the most common culprit in such situations. The MS tools have that on by default.
    Remember...the application works fine when the column exists, is non-nullable, and has a default assigned to it. QUOTED_IDENTIFIER would not seem to affect the creation of an identity value.

    Weird, isn't it? I have a call in to the developer's support, but I'm not holding my breath on an answer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I know this issue, but have to research more. Unfortunately, I have only one DBA working for me and time is a premium.

    This is an application layer problem. I know it, but can't recall specifics. Needless to say, I don't use embedded SQL in my applications.

    -----------------------------------------------------------------------

    ' Check to see if new user was selected from list and exit this sub if true
    If cboUsers.Text = "New" Then
    txtUserName.Focus()
    Exit Sub
    End If
    ' Select clare user menu items
    strSql = "exec sp_get_users '" & cboUsers.Text & "'"
    Dim adapter As SqlDataAdapter = New SqlDataAdapter()
    Dim command As New SqlCommand(strSql, clsConnect.conProd)
    Dim table As New DataTable

    -----------------------------------------------------------------------

    I develop VB.NET applications to support my DBA work. Just another side job to keep me entertained.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I'd still be interested in what the trace is showing. Do you have a QA environment where the traffic to the application can be a bit more limited? I believe the login event and current session event both contain the various set options in the text data. I have to believe an error is getting generated somewhere. Either that, or something is just rolling the whole thing back.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Victory!

    I discovered that the application was, as part of a single transaction, also attempting to insert to a third table "C". One of the values it attempted to insert was the ID from table "A", which is itself an identity. The application must be using SCOPEIDENTITY to get the new ID from table A, but by adding the identity to table B, it was picking up that instead. Trying to insert the ID from table B into table C was causing primary key violations.
    My solution will be to use GUIDs for any surrogate keys I need to add.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Have you looked at audit tools, either third party or MS?

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Audit tools? We're setting up replication.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Replication ?!?!?

    You did say replication in your first post, but I'd assumed that this was an auditing table from the git-go.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I read it as replication from this line:
    Quote Originally Posted by blindman
    We're trying to replicate some data tables, which means they must have primary keys.

Posting Permissions

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