SQL Server 2008: Auto increment column

HI!

I need some help how to increment a column (RevisionNumber) depending of ProductType.

So if I have [ProductType] 10 and 20 both with [RevisionNumber]  1, if I then I insert a new ProductType 10, then I like RevisionNumber to be 2, and the next time I insert a new ProductType 20 I like that RevisionNumber also to be 2, (not 3 if you use default Auto Increment)

So I only want to increment  [RevisionNumber] with +1 from the number it already has, so if it has 4 I like it to be 5 regarding of what the Auto Increment has done on another product type.

Is this possible?
LVL 1
AWestEngAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Ok, Try using the below approach:

1. Create AFTER TRIGGER for INSERT statements in that particular table to update RevisionNumber column for a particular record.(Update with the Max value + 1 for the particulary ProductType)
2. Create a unique index on ProductType column and RevisionNumber column.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is possible, but not built-in.you might need to use either a procedure to handle the insert (and find out the highest previous revision number for the product_type, +1, or a trigger to do this ...what's your take ...
AWestEngAuthor Commented:
The ProductType and RevisionNumber column have Primary keys on both.
And I think a trigger maybe best here.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is that you need another primary key, actually, to be working correctly. ..

anyhow, this trigger assumes that you insert "0" as RevisionNumber for "autoassignment"
create trigger on yourtable for insert 
as
begin
  update t
      set r.RevisionNumber =1 + isnull( ( Select max(x.RevisionNumber) from yourtable x where x.ProductType = i.ProductType ) , 0 )
    from yourtable t
   where t.RevisionNumber is null
      or t.RevisionNumber = 0  
end

Open in new window

AWestEngAuthor Commented:
hmm I get this error
 
Msg 4104, Level 16, State 1, Procedure AutoIncrementRevisionNumber, Line 16
The multi-part identifier "r.RevisionNumber" could not be bound.
 
any tips?
AWestEngAuthor Commented:
if i changed the "r" to "t" and the "i" to "x2, it worked to insert the trigger, is that correct then?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry about the typos:
create trigger on yourtable for insert 
as
begin
  update t
      set t.RevisionNumber =1 + isnull( ( Select max(x.RevisionNumber) from yourtable x where x.ProductType = t.ProductType ) , 0 )
    from yourtable t
   where t.RevisionNumber is null
      or t.RevisionNumber = 0  
end

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AWestEngAuthor Commented:
Perfect, thx man
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.