Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: trigger problem: alternative to identity fields?

    Hello,

    This might take a little explaining, please hope someone is patient to suggest a solution.

    Summary of details below:

    In my table I can't use an identity field, so use a function to generate each row id. but it doesn't work for multiple inserts.

    Read on for more fun!

    Details...


    An insert into t_MyTable fires a trigger which in turn inserts a row into AUDIT_LOG.

    For simplicity, say my two tables are this:
    t_myTable

    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [data] [varchar] (50) NOT NULL

    AUDIT_LOG
    [AUDIT_LOG_ID] [int] NOT NULL ,
    [change_descn] [varchar] (50) NOT NULL

    I want AUDIT_LOG_ID to be unique,
    But I can't use the identity field and my alternative appears to be flawed.

    Why can't I use Identity? Because it changes @@Identity and I don't want this to change as I want @@Identity to have the value of the identity field for the row inserted in t_MyTable.

    My problem stems from bulk inserts. My trigger looks like this:

    declare @Iterations
    set @Iterations = @@ROWCOUNT

    while @Iterations > 0
    begin
    INSERT AUDIT_LOG
    [change_descn]
    SELECT
    'rec inserted'
    FROM INSERTED new
    WHERE new.[id] IS NOT NULL

    set @iterations = @iterations - 1
    end

    (which i think will cope with bulk inserts ok).

    I've used a user defined function as the default value for AUDIT_LOG_ID (which I want to be unique). Function looks like this:

    DECLARE @FunctionReturn int

    IF (SELECT COUNT(*) FROM AUDIT_LOG) = 0
    SET @FunctionReturn = 1
    ELSE
    BEGIN
    SELECT TOP 1 @FunctionReturn = max(AUDIT_LOG_ID + 1) from AUDIT_LOG
    GROUP BY AUDIT_LOG_ID
    ORDER BY AUDIT_LOG_ID desc
    END

    RETURN (@FunctionReturn)
    END

    The function works for individual inserts, but when I do a bulk insert it only generates one AUDIT_LOG_ID and therefore my AUDIT_LOG table gets multiple rows written all with the same AUDIT_LOG_ID. (NB, the trigger and row id work fine when I use an Identity field on AUDIT_LOG_ID, but as mentioned before i can't use this).

    So, when I have the identity field on, the AUDIT_LOG table data looks like this for a bulk insert of 4 rows.
    1 'rec inserted'
    2 'rec inserted'
    3 'rec inserted'
    4 'rec inserted'

    when i use my function to generate the AUDIT_LOG_ID, the table data looks like this (non-unique AUDIT_LOG_IDs )
    1 'rec inserted'
    1 'rec inserted'
    1 'rec inserted'
    1 'rec inserted'




    Phew...
    Any work arounds or suggestions v greatly appreciated. Thanks
    Graeme, UK

  2. #2
    Join Date
    Jul 2003
    Posts
    1

    Reference

    You may want to read this article that could gives you a new perspective on your problem.

    http://msdn.microsoft.com/library/de...anidcrisis.asp

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Two things:

    First, it is unnecessary to loop through iterations in your trigger. The "inserted" virtual table contains 1 row for each record inserted, so you can just write a statement that says

    insert into Audit_Log (change_descn)
    select 'rec inserted'
    from inserted
    where inserted.id is not null

    (...not sure WHY you would want to do this, but I assume you are collecting more information than what you have described...)

    Secondly,
    Your Audit_Log.id problem is easily solved changing your id field datatype to UniqueIdentifier and making its default the built-in function NewID().

    blindman

  4. #4
    Join Date
    Jul 2003
    Posts
    2
    Thanks to both of you for suggestions. The article makes good reading.

    Due to elsewhere in the system expecting the AUDIT_LOG_ID to be an integer, I've decided to work around the @@Identity mess-up by using SCOPE_IDENTITY to return the record id from t_myTable. I think this should work.

    Graeme

  5. #5
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    You might check out IDENT_CURRENT('table_name') also; I NOT sure what you are doing so it might not do what you want.

    If multiple users will be doing inserts to the table IDENT_CURRENT is NOT likely what you want.

    Tim S
    Last edited by TimS; 07-13-03 at 02:38.

  6. #6
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Haven't read much of this but:
    SCOPE_IDENTITY returns the last identity in the scope of the statement so will not be affected by the trigger but is specific to the spid.

    Another option is to have a trigger on the audit table

    set rowcount 1
    while exists (select * from AUDIT_LOG where AUDIT_LOG_ID is null)
    update AUDIT_LOG
    set AUDIT_LOG_ID = (select max(AUDIT_LOG_ID + 1) from AUDIT_LOG)
    where AUDIT_LOG_ID is null
    set rowcount 0

    You can get it a lot more efficient than that and should probably use a second table to hold the id

    select @id = id from idtbl (tablockx)
    set rowcount 1
    while exists (select * from AUDIT_LOG where AUDIT_LOG_ID is null)
    update AUDIT_LOG
    set @id = AUDIT_LOG_ID = @id+1
    where AUDIT_LOG_ID is null
    set rowcount 0
    update idtbl set id = @id

Posting Permissions

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