How to create trigger for custom Primary Key field?

• arman celestial

  SSC Rookie

  Points: 46

  More actions

  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

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  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.

• arman celestial

  SSC Rookie

  Points: 46

  More actions

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  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