Solved

SQL trigger to return unique code

Posted on 2009-07-14
15
277 Views
Last Modified: 2013-11-27
Hi,

I have recently upgraded my MS Access program to SQL BE and i'm trying to get a SQL trigger setup.  When the tb_quotation01 table is updated i need to populate the the quotation_number with a unique number generated from the customer_code entered, a number between 001 and 999 with QU appended to to the start (eg. QU255001).

This quotation_number needs to then be returned to the original form and populated into the quotation_number filed so the user can see it.

I hope someone can assist me.

Regards,
Tom
0
Comment
Question by:d10u4v
  • 7
  • 4
  • 2
  • +1
15 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24855451
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
;


0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24855452
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
;
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24855482
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
;

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24855499
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.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24855752
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
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24855868
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24855990
>>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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24855999
Trigger Logic can do that sometimes, :-)
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24856206
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24856237
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.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24856468
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.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24856761
Yeah - and a scalar expression that uses a query can't act as a default either.
0
 

Author Comment

by:d10u4v
ID: 24856905
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!
0
 

Author Comment

by:d10u4v
ID: 24927430
Can anyone advise on this topic?  I'm having a few issues.
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24931878
If you're using a stored procedure, then it makes life much easier.

Pass in your list of parameters, including @customer_code, and then have an insert statement that looks something like:

insert tb_quotation01 (col1, col2, ..., quotation_number)
output inserted.quotation_number
select @param1, @param2, ....., 'QU' + right('000' + cast(@customer_code as varchar(3)),3) + right('000' + cast((select count(*) + 1 from tb_quotation01 t2 where t2.customer_code = @customer_code) as varchar(3)),3)
;

Then your stored procedure will produce a resultset of your quotation_number field and you'll be able to pick this up easily in your application.

Rob
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now