SQL trigger to return unique code

How does this grab you?

update t set quotation_number = 'QU' + right('000' + cast(t.customer_code as varchar(3)),3) + right('000' + cast((select count(*) + 1 from tb_quotation t2 where t2.customer_code = t.customer_code) as varchar(3)),3)
from
  tb_quotation01 t
  join
  inserted i
    on t.id = i.id
;

Sorry, typo:

update t set quotation_number = 'QU' + right('000' + cast(t.customer_code as varchar(3)),3) + right('000' + cast((select count(*) + 1 from tb_quotation01 t2 where t2.customer_code = t.customer_code) as varchar(3)),3)
from
  tb_quotation01 t
  join
  inserted i
    on t.id = i.id
;

And then to return it, you could have your trigger do something like:

select t.id, t.quotation_number
from
  tb_quotation01 t
  join
  inserted i
    on t.id = i.id
;

Will that work on a multiple line insert or will that matter to you?  And triggers don't really return value to the calling program, that is up to stored procedures and such.

update t set quotation_number = 'QU' + right('000' + cast(t.customer_code as varchar(3)),3) + right('000' + cast((select count(*) + i.rownum from tb_quotation01 t2 where t2.customer_code = t.customer_code) as varchar(3)),3)
from
  tb_quotation01 t
  join
  (select *, row_number() over (partition by customer_code order by id) as rownum from inserted) i
    on t.id = i.id
;

This will make sure that if you insert multiple records for the same customer_code, that they get separate quotation_numbers.

That what you mean, CGL?

Rob

Yeah Rob, that was what I was refering to.   It is complicated to try to do something like this in a trigger, seems like a SP would be a better place.  The asker has not responded to see if any of this even matters to them.
Cheers,
Chris

>>The asker has not responded to see if any of this even matters to them.<<
Actually, I could be wrong, but despite Rob's best intentions I get the feeling it may have gone over their head.

Trigger Logic can do that sometimes, :-)

Actually, I'd prefer to give them a totally unique number using an identity field, so that the OUTPUT clause could do it.

So... suppose you had an id field... make a computed column like:

alter table tb_quotation01
add quotation_number as 'QU' + right('000' + cast(customer_code as varchar(3)),3) + right('000000000' + cast(id as varchar(10)),10) NOT NULL
;

So then you just use the OUTPUT clause on the insert:

INSERT tb_quotation01 (customer_code, otherdetails)
OUTPUT inserted.id, inserted.quotation_number
VALUES (55, 'Here are some other details');

But if you need the postfix to be generated on a per-customer basis, you can't use the 'newly generated id' concept so easily.

I forget - can a default value use a function that involves a query?

Rob

Perhaps a better (certainly more scalable) approach would be to store the next value in the customer table (or in a separate table), that way you grab it and increase by one and add all the other formatting characters.  Quite a few different ways to skin this cat.

I like that answer Rob, I was thinking of a combination of computed column and Identity maybe for the incrementing number.
And nope, "Subqueries are not allowed in this context. Only scalar expressions are allowed."  You cannot use any kind of query in the default value.

Yeah - and a scalar expression that uses a query can't act as a default either.

Hi

Thanks for the super response!  I had been looking at this all evening trying to figure it out and asked the question just before going to bed :) sorry for the delay in responding.

With regards to retiring the quotation number to the program; I only need this so that the user sees the quotation number which has been generated.  Would it be possible to generated the new quotation number using the trigger and then requery the form?

If not, how would the stored procedure method work.  As you can see, I'm a little new to the SQL side if things, but very willing to learn.

Thanks so much for all your help so far!

Can anyone advise on this topic?  I'm having a few issues.