July 10, 2013 at 9:19 am
Hi all, I"m very new to SQL and have been searching the forums but haven't found what I'm looking for. I need to create 2 additional auto increment fields in a table. We are using SQL 2008 R2. I think I need to use a trigger to do this, but I'm not sure exactly the best way to do this. Here is my table structure.
Employee_Number is IDENTITY field
First_Name
Last_Name
Library_Barcode - needs to be auto increment
Food_Barcode - - needs to be auto increment
The 2 barcode fields need to be auto increment fields when a new record is added. I was thinking of having another table that would contain the last value used for these 2 fields and then create a tirgger when a new record was added that would read the values and add 1 and insert the new value into their respective barcode fields above and then update the table containing the last number used. I wasn't sure if this was the best method to perform what I'm looking for. The numbers for these fields are only added when a new record is created, they will never change.
Any help would be appreciated.
Kevin
July 10, 2013 at 9:34 am
The only auto-increment in SQL 2008 is the identity. You can try and roll your own increment (easy to get wrong) or maybe you can set up the other two columns so that they are calculated columns based off the identity and some other details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2013 at 9:51 am
I would model it differently, to avoid customly controlled auto increments.
What I would do is:
1. remove Library_Barcode and Food_Barcode columns from your Employee table
2. create two separate tables Employee_Library and Employee_Food. Each of these tables will have
two columns: Barcode IDENTITY and Employee_Number as FK to Employee table.
3. create simple FOR INSERT trigger which will just insert Employee_Number's of the newly insereted employee records into Employee_Library and Employee_Food. Which will cause two independent IDENTITY generated automatically.
Trigger body will be very simple:
SET NOCOUNT ON;
INSERT Employee_Library (Employee_Number) SELECT Employee_Number FROM inserted;
INSERT Employee_Food (Employee_Number) SELECT Employee_Number FROM inserted;
July 10, 2013 at 12:01 pm
Gail, thanks, I was going to do some testing on controlling it myself. Unfortunately, there is no was to use the Identity field to calculate off of. It baffle's my mind as to why you can only have 1 auto increment field in a DB. Just makes no sense to me.
SSCrazy, thanks for the suggestion but unfortunately I can't remove the 2 fields from the existing table. As this is an existing SQL Table for another application. The customer is currently manually tracking the next barcode numbers and entering then during data entry. They were hoping we could automate this for them.
July 10, 2013 at 12:04 pm
ktager (7/10/2013)
Gail, thanks, I was going to do some testing on controlling it myself. Unfortunately, there is no was to use the Identity field to calculate off of.
Why not? If they're both supposed to auto-increment, then it follows that one of them is x+the other one, unless you're incrementing by different steps, at which point it becomes x+y*the other one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2013 at 12:44 pm
Gail,
I'm not sure that would work either. The employee number is a 7 digit number and 1 barcode is only 4 digits and the other is 12 digits. Seems like an awful lot of calculating to produce the values. I looking to see if we can update to SQL 20012 and use the new SEQUENCE function.
July 10, 2013 at 1:11 pm
If you can't go SQL2K12 and need to write your own function/table to track this, make sure it's transactionally controlled to prevent concurrency issues.
July 10, 2013 at 1:12 pm
Ok, so how is the Employee number calculated, how is the barcode calculated? How is the Employee food calculated? Are they all just straight incrementing from the previous number?
Can you post say 10 rows of data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2013 at 2:54 pm
Below is sample data. As I stated the Library and Food Numbers were originally being manually tracked, but they are just an auto increment by 1 when a new record is added. EmployeeID is an IDENTITY Field
EmployeeIDFirstLastLibraryFood
5000000 JoeJones1832880101802147
5000001 MaryJackson1833880101802148
5000002 JimSingleton1834880101802149
5000003 AlbertSmith1835880101802150
5000004 KevinSmith1836880101802151
5000005 TomClancy1837880101802152
5000006 AliceRodriguez1838880101802153
5000007 SimonSchultz1839880101802154
5000008 KimWagner1840880101802155
5000009 DavidBlaine1841880101802156
July 10, 2013 at 3:20 pm
Contrived, but maybe you'll be able to use this as a starting point.
CREATE TABLE Employees (
ID INT IDENTITY(0,1),
EmployeeID AS ISNULL(ID+5000000,0) PERSISTED,
FirstName varchar(50),
LastName varchar(50),
Library AS (ID+1832) PERSISTED,
Food AS (ID+880101802147) PERSISTED
)
ALTER TABLE Employees ADD CONSTRAINT pk_Employees PRIMARY KEY CLUSTERED (EmployeeID)
INSERT INTO Employees (FirstName, LastName) VALUES
('Joe','Jones'),
('Mary','Jackson'),
('Jim','Singleton'),
('Albert','Smith')
SELECT * FROM dbo.Employees AS e
The problem will be if there are any gaps in the existing data (any places where the increment from 'previous' is not the same for all three numbers. Maybe not an elegant approach though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2013 at 3:41 am
...
SSCrazy, thanks for the suggestion but unfortunately I can't remove the 2 fields from the existing table. As this is an existing SQL Table for another application. The customer is currently manually tracking the next barcode numbers and entering then during data entry. They were hoping we could automate this for them.
First of all I'm not SSCrasy 😉
It's still possible to do if you want robust auto increment using identity.
Howevere it will involve a bit more DB restructure. I don't know if you would wish to do so, however here is the way:
Rename your Employee table to something like Employee_Base.
Create a view named Employee which will be a JOIN between Employee_Base and these two tables I've siggested in my previous post.
Create INSTEAD OF triggers for INSERT,UPDATE and DELETE on this view, so users of that view will see it and can use it as a table.
Yes, it is more work, but you will have robust and independent generation of your ID's, which will be way better than any custom based auto-increment solution untill you move to SQL2012 and use SEQUENCES.
July 11, 2013 at 9:51 am
SSCrazy, sorry for the miss spell of name 😀
I think we are going to utilize the Identity field and add value to get next barcode numbers / employeeID as in Gail's example.
I'm testing it now. thanks to all for their feed back, it was much appreciated. Still can't understand why a table can't have more than 1 auto increment field.
Kevin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply