# Supervise numbers by trigger

Page 1 of 1

## 5 Replies - 585 Views - Last Post: 21 September 2016 - 05:00 AM

### #1 nighttrain

• D.I.C Regular

Reputation: 8
• Posts: 484
• Joined: 22-September 10

# Supervise numbers by trigger

Posted 20 September 2016 - 03:15 AM

I have multi user application. Users can create products and those products belongs to producents. Every producent has unique number. Each main product can contain sub-products or not.

Product number is constructed with three parts in this way:

```{producent number}.{product number}.{main/sub product number}
```

2nd part:

if it's the first producent's product number it always starts from: 1001 then 1002, 1003 till it's reaches 9999 and then 10001 till 99999.

3rd part:

Main product is always 1 and all his sub-products (if exists) has 2,3,4 etc..
Example of products in table:

```143.1001.1 - main product
143.1001.2 - sub product
143.1001.3 - sub product
143.1001.4 - sub product
143.1002.1 - main product
143.1002.2 - sub product
143.1003.1 - main product
6.1001.1 - main product
6.1002.1 - main product
6.1002.2 - sub product
```

In my application using query (shown below) and passing producent number i am able to get next number. So for instance:

```for producent 143 next number my query will return: 1004
for producent 6 next number my query will resturn : 1003
for producent 15 next number my query will resturn: 1001 (no products for this producent therefore 1001)
```

```rest parts 1st & 3rd is added from appliation and then inserted to db
```

SQL query:
```SELECT CASE WHEN r.number Is NULL THEN 1001
WHEN r.number = 9999 THEN 10001
Else r.number + 1 End number
FROM (VALUES(@PRODUCENTNUMBER)) AS a(art)
LEFT JOIN(SELECT PARSENAME(Nummer, 3) art,
MAX(CAST(PARSENAME(Nummer, 2) AS INT)) number
FROM Article WHERE Nummer Like @PRODUCENTNUMBER + '[.]%'
GROUP BY PARSENAME(Nummer, 3)
) r
On r.art = a.art
```

What's the issue then? The issue is within mentioned application when users get next product number it's not inserted to db straighaway but keep in variable and could be situation that untill it's not inserted other users could get also same number. Therefore from my point of view we need trigger on db level so when first user insert his product (note that it's done by transaction if that does matter) so for rest users which keeps their products based on same number (2nd part) when it comes to their insert trigger would rechange it (BEFORE INSERT trigger?) as recognized that this number is already added. Also column vater has to be changed (vater is just main product number) - shown in fiddle.

I prepapred some schema and data so we can work together to achieve the goal. If something is unclear let me know.

Fiidle: http://sqlfiddle.com/#!6/069cd/7/0

Table is simple:

```ID int (PK) is identity yes with auto incremement
Nummer (varchar)
Amazon (varchar)
Vater  (varchar)
```

This post has been edited by nighttrain: 20 September 2016 - 03:17 AM

Is This A Good Question/Topic? 0

## Replies To: Supervise numbers by trigger

### #2 modi123_1

• Suitor #2

Reputation: 13562
• Posts: 54,111
• Joined: 12-June 08

## Re: Supervise numbers by trigger

Posted 20 September 2016 - 06:57 AM

Quote

when users get next product number it's not inserted to db straighaway but keep in variable and could be situation that untill it's not inserted other users could get also same number.

Why wouldn't you have a query that checks if the product number is available before inserting the data? If not then stop the 'save', throw the user a message that the number needs changing, and let them handle it?

### #3 baavgai

• Dreaming Coder

Reputation: 6996
• Posts: 14,635
• Joined: 16-October 07

## Re: Supervise numbers by trigger

Posted 20 September 2016 - 07:45 AM

The sql fiddle link sadly didn't work. Ok, aside from an unfortunate design...

Why would you present any numbers to your users at all? Until you have data to hang this on, what's the point?

For design... ?!? ... perhaps:
```Product
-- ProducentNum int (PK, FK Producent)
-- ProductMajorNum int (PK)
-- ProductMinorNum int (PK)

```

I mean, at the very least, don't you want to Product require a valid Prodcent? How do you have any data integrity with this stringy "smart" number thingy? In any case, it's clearly an efficiency hit to do something like that.

Also, sub product sounds like a call for a child table.

### #4 DarenR

• D.I.C Lover

Reputation: 593
• Posts: 3,823
• Joined: 12-January 10

## Re: Supervise numbers by trigger

Posted 20 September 2016 - 10:00 AM

seems liek what you are trying to do is to cumbersome. Baavgai just showed you a totally simple way of doing your products. If i were you i would use his suggestion

### #5 nighttrain

• D.I.C Regular

Reputation: 8
• Posts: 484
• Joined: 22-September 10

## Re: Supervise numbers by trigger

Posted 20 September 2016 - 03:20 PM

hanks for all suggestion about data normalization - i am also aware about that but unfortunetly it's old developer implementation and i cannot change it, but have to work on how it is. There is reason why product numbers are associated before insert and not directly created on insert (also old implementation that have to stay and i suggested to change but without success from customer side). The only one way for me is to prepare trigger which would check numbers in table and change/replace before insert. Could you help me to prepare one?

The link should work i ust checked: http://sqlfiddle.com/#!6/069cd/7/0

### #6 baavgai

• Dreaming Coder

Reputation: 6996
• Posts: 14,635
• Joined: 16-October 07

## Re: Supervise numbers by trigger

Posted 21 September 2016 - 05:00 AM

Link didn't work, the trailing zero threw it. This did, though: http://sqlfiddle.com/#!6/069cd/7

Ok, I wouldn't use a trigger. The trick is that you want to pull the next available value and then make sure no one else gets it. The only real way to do this would be to create a record as a place holder. Then, the next call will get the next value.

I'd recommend a stored procedure for this. The stored procedure will not only give you the next value, but also create the place holder. Basically, no one will EVER insert on the table, only update. This is actually fairly common.

So:
```CREATE TABLE Article (
[ID] int identity(1,1) not null,
[Nummer] varchar(MAX),
[Amazon] varchar(MAX),
Vater varchar(MAX)
);
go

INSERT INTO Article([Nummer], [Amazon], [Vater])
VALUES
('12.1001.1', 'HTG', '12.1001.1'),
('12.1001.2', 'RGT', '12.1001.1'),
('12.1001.3', 'EEE', '12.1001.1'),
('6.10001.1', 'FFF', '6.10001.1'),
('6.10001.2', 'DSA', '6.10001.1'),
('216.10001.1', 'dd', '216.10001.1'),
('15.1001.1', 'FFF', '15.1001.1'),
('15.1001.2', 'DSA', '15.1001.1'),
('15.1002.1', 'FFF', '15.1002.1'),
('15.1003.1', 'DSA', '15.1003.1'),
('12.1002.1', 'HTG', '12.1002.1'),
('12.1003.1', 'RGT', '12.1003.1'),
('12.1003.2', 'EEE', '12.1003.1'),
('15.9999.1', 'dd3', '15.9999.1'),
('15.9999.2', 'dd3', '15.9999.1');
go

create procedure UpGetArticleNext
@Art int,
@Nummer as varchar(MAX) OUTPUT
as begin
declare @NextNumber int
SELECT
@NextNumber = MAX(CAST(PARSENAME(Nummer, 2) AS INT))
FROM Article
WHERE Nummer Like cast(@Art as varchar) + '[.]%'
GROUP BY PARSENAME(Nummer, 3)
if @NextNumber is null set @NextNumber=1000
if @NextNumber=9999 set @NextNumber=10000
set @Nummer = cast(@Art as varchar) + '.' + cast((@NextNumber + 1) as varchar) + '.1'
insert into Article([Nummer]) values(@Nummer)
end
go

declare @Nummer varchar(MAX)
exec UpGetArticleNext 15, @Nummer OUTPUT
print @Nummer

exec UpGetArticleNext 15, @Nummer OUTPUT
print @Nummer
go

select * from Article where Nummer like '15.%'
go

```

Note that I've made the ID sensible. If this were me, I'd just pass back the ID. If the ID is not the primary key and Nummer is, then get rid of ID; it's clutter.

In keeping with your fiddle preference: http://sqlfiddle.com/#!6/cfe68/1

Hope this helps.