Post reply

How to create trigger for custom Primary Key field?

  • October 7, 2008 at 1:00 pm

    #194456

    Hi,

    I'm a newbie in creating triggers in SQL Server.

    How do I create a trigger for automatically populating a custom primary key field in the format: ABC200810071, where ABC is static alpha data, 20081007 is current date in this format and 1 is a sequential number.

    For example, if I have to add two new records today, the primary keys are:

    ABC200810071

    ABC200810072

    If I insert two records tomorrow:

    ABC200810081

    ABC200810082

    Any help is much appreciated!

    Thanks,

    Arman

  • October 7, 2008 at 1:15 pm

    #881927

    Avoid a trigger.

    You can do this with an identity column and then a computed column that concatenates everything together for you.

    I would suggest that you pad the incrementing number to enough digits that your column will sort in the correct order.

  • October 7, 2008 at 1:24 pm

    #881931

    Thank you for your post and suggestions!

    I have not done a customized Identity before. Could you provide some details or links? Also, the format that I wanted, the sequential number starts at 1 again for a new day. Can this be done using Identity?

    Arman

  • October 7, 2008 at 1:43 pm

    #881945

    Shame on you.

    You are taking what should be three different columns and making one column out of them. Keeping date and sequence separate in your db will save you headaches in the long run, and you can always put this string together when you want to show it to users.

    But if you insist, this will get you started. It should work for up to 999 pk events in a day. The samplefile CTE is just for test purposes. Try changing the last pk to end in 7003 instead of 6003. It is NOT totally debugged nor thoroughly tested. It is a starting point for you, not a finished product. It comes without warranty of any sort, so use at your own risk.

    Also, remember that if an insert causes multiple rows to be inserted, the trigger will only fire once. The code below doesn't account for this possibility.

    Good luck.

    ==========================================

    declare @lastdate datetime

    declare @maxPK varchar(20)

    ;with sampleFile (pk) as

    (select 'ABC20081006001'

    union all

    select 'ABC20081006002'

    union all

    select 'ABC20081006003'

    )

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

    select @maxPK = max(pk) from sampleFile

    select @lastDate = substring(@maxPK,4,8)

    --select @lastDate

    select @lastDate = @lastdate+1

    --select @lastDate

    select case when getdate() >= @lastDate then 'ABC'+convert(char(8),getdate(),112)+'001'

    else 'ABC'+convert(varchar(12),right(@maxPK,len(@maxPK)-3)+1)

    end

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 4 posts - 1 through 3 (of 3 total)

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