Post reply

How to generate a batch id?

  • July 23, 2008 at 8:48 am

    #194320

    Hi,

    I have trigger that tracks changes to a table. I insert the changes into a table if it is a insert/update/delete. The table looks like this:

    id (sequence number)

    batch_id (identifies a set of related changes; i.e. multiple values were modified in one record with the same action)

    table (modified)

    column (modified)

    original_value

    new_value

    user_id

    timestamp

    Now, my problem is how to generate a batch id that is not too large, but has to be unique and should count up (for example, batch id = 200, next batch id should be 201 etc etc). So, if an insert happened, the batch id would be the same for all of those rows (on row per column that was inserted).

    Any idea?

    I tried NEWID(), but it generates a huge number.

  • July 23, 2008 at 9:02 am

    #847111

    you could get max(batchID) in your trigger and use plus 1 for this batch.

    You might have concurrency problems if you do this.

    Another solution will be

    Have table for next Id value (TableName, NextID)

    , write a function that grabs the Next ID and increments the NextID value and grab it

    Use this funciton at your batch start.

  • July 23, 2008 at 9:04 am

    #847117

    one idea would be to restructure your table to let batch id be a computed column. the computed column would reference a function you create that would decipher the rows from the trigger to decide if a new id is created or reuse of a same batch id.

    hth

  • July 23, 2008 at 9:05 am

    #847118

    You could use a separate table that tracks the last batch id used

  • July 23, 2008 at 9:13 am

    #847126

    Steven Cameron (7/23/2008)

    You could use a separate table that tracks the last batch id used

    Yes, and you could move batch-common stuff like the UserID into that table. That would be more normalized.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 23, 2008 at 10:14 am

    #847176

    Thanks.

    So if I have a seperate table which contains one thing, batch id.

    I then write a function to retreive this batch id, but how do I update the batch id to the next batch id?

    I can't use UPDATE inside a FUNCTION it seems...

  • July 23, 2008 at 10:23 am

    #847190

    Sorry about that...it should be a stored procedure.

  • July 23, 2008 at 10:30 am

    #847193

    Yes, that's what I thought. I now have a table with the next batch id. This seems to work.

    Thanks guys.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply