Help with SQL trigger for creating Barcodes

Other SQL Server Topics
1

Hello, first timer on this forum

I need some help to modify this trigger witch is made to create a gtin code when a new product is inserted on Prod table.

It works well with a 6 digit prefix and 6 digit counter, but must be adjusted for 7 digit prefix and 5 digit counter.

This is the trigger i use:

ALTER TRIGGER [dbo].[utr_Prod_EAN13]
ON [dbo].[Prod]
AFTER INSERT, UPDATE
AS
BEGIN

SET NOCOUNT ON;
Declare
@ProdNo Varchar(40),
@CompanyPrefix Varchar(40),
@Counter Varchar(40),
@EAN13 Varchar(40),
@Checkdigit INT,
@Barcode varchar(12)

Select @ProdNo = i.ProdNo,@EAN13 = i.EANItmNo
from inserted i
if update(ProdNo) and @EAN13=''

begin
    set @CompanyPrefix = (select Val1 from StrFVar where NmSp = 'EANcode' and VarNm = 'companyprefix')
    set @Counter = (select Val1 from StrFVar where NmSp = 'EANcode' and VarNm = 'counter')
    set @Barcode =  @CompanyPrefix + @Counter
    set @Checkdigit = 
            (10 - (CAST(SUBSTRING(@Barcode, 1, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 2, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 3, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 4, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 5, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 6, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 7, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 8, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 9, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 10, 1) AS INTEGER)
            --+ CAST(SUBSTRING(@Barcode, 11, 1) AS INTEGER)
            --+ 3* CAST(SUBSTRING(@Barcode, 12, 1) AS INTEGER)
           )%10
     )%10
    set @EAN13 = @CompanyPrefix + @Counter + Cast(@Checkdigit as varchar(1))
    update Prod set EANItmNo = @EAN13 where ProdNo = @ProdNo
    update StrFVar set Val1 = (select FORMAT(CAST(@Counter as int) + 1, '000000')) where NmSp = 'EANcode' and VarNm = 'counter'
END

END
GO

Prefix + counter:
EANcode|companyprefix 7073306

EANCode|Counter 100012

Gives this output in Prod table:

ProdNo / Gtin code

As you can see, the GTIN created has 14 digits, but should only have 13
Counter must be adjusted to 5 digits, but what do i need to do to modify the trigger..?

That is my question..?

|1|70733061000005|
|2|70733061000015|
|3|70733061000025|
|4|70733061000035|
|5|70733061000045|
|6|70733061000055|
|7|70733061000065|
|8|70733061000075|
|9|70733061000085|
|10|70733061000095|
|11|70733061000105|
|12|70733061000115|

2

A litle correction.
This is the active trigger i use:

ALTER TRIGGER [dbo].[utr_Prod_EAN13]
ON [dbo].[Prod]
AFTER INSERT, UPDATE
AS
BEGIN

SET NOCOUNT ON;
Declare
@ProdNo Varchar(40),
@CompanyPrefix Varchar(40),
@Counter Varchar(40),
@EAN13 Varchar(40),
@Checkdigit INT,
@Barcode varchar(12)

Select @ProdNo = i.ProdNo,@EAN13 = i.EANItmNo
from inserted i
if update(ProdNo) and @EAN13=''

begin
    set @CompanyPrefix = (select Val1 from StrFVar where NmSp = 'EANcode' and VarNm = 'companyprefix')
    set @Counter = (select Val1 from StrFVar where NmSp = 'EANcode' and VarNm = 'counter')
    set @Barcode =  @CompanyPrefix + @Counter
    set @Checkdigit = 
            (10 - (CAST(SUBSTRING(@Barcode, 1, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 2, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 3, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 4, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 5, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 6, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 7, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 8, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 9, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 10, 1) AS INTEGER)
            + CAST(SUBSTRING(@Barcode, 11, 1) AS INTEGER)
            + 3* CAST(SUBSTRING(@Barcode, 12, 1) AS INTEGER)
           )%10
     )%10
    set @EAN13 = @CompanyPrefix + @Counter + Cast(@Checkdigit as varchar(1))
    update Prod set EANItmNo = @EAN13 where ProdNo = @ProdNo
    update StrFVar set Val1 = (select FORMAT(CAST(@Counter as int) + 1, '000000')) where NmSp = 'EANcode' and VarNm = 'counter'
END

END
GO

3

You should look at this part:

update StrFVar set Val1 = (select FORMAT(CAST(@Counter as int) + 1, '000000')) where NmSp = 'EANcode' and VarNm = 'counter'

If the counter is 1 then you are adding 1 + 000000 giving the result 000002. When you adjust the 6 zero's to 5 zero's it should give the result required.

1 Like
4

Thanks!

You are 100% correct.

Output is now 13 digits like i wanted to
Result is also corrct according to check digit calculator :grinning:

The last digit of a barcode number is a computer check digit which makes sure the barcode is correctly composed. Use our check digit calculator below to calculate a check digit.

Calculate a check digit
GS1 key without check digit
707330610128
707330610128
6
Your Check Digit
Copy this key
See this in a 14-digit GTIN format
You've entered 12 digits, this corresponds with the GTIN-13 format. It could also be a GLN or the first 13 digits of a GRAI, GDTI or GCN.
7073306101286

|1|7073306100005|
|2|7073306100012|
|3|7073306100029|
|4|7073306100036|
|5|7073306100043|
|6|7073306100050|
|7|7073306100067|
|8|7073306100074|
|9|7073306100081|
|10|7073306100098|
|11|7073306100104|
|12|7073306100111|
|13|7073306100128|
|14|7073306100135|
|15|7073306100142|
|16|7073306100159|
|17|7073306100166|
|18|7073306100173|
|19|7073306100180|
|20|7073306100197|

5