0

I am trying to create an auto sequence trigger for my UniqueID field in SQL Server that is dependent on multiple fields. For example I have a table called animalswith columns Species, Sex, UniqueID. I am trying to create a trigger for when species and sex is entered the UniqueID field is auto populated based on the last sequential number for that species and sex. Needs to look like DeerM0001.This is what I have so far but it doesn't work. 

CREATE TRIGGER tr_animalID
ON  animal
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @ID varchar(8)
    SET @ID = (SELECT max(Species) FROM animal);

    UPDATE animal
    SET UniqueID = CONCAT(Species, Sex) +0001 
    WHERE UniqueID = @ID;
END
GO
Dale K
  • 21,987
  • 13
  • 41
  • 69
JNN
  • 109
  • 7
  • So must you have a separate sequence per combination e.g. you require `DeerM0001` & `DeerF0001` - you couldn't have `DeerM0001` & `DeerF0002` – Dale K Nov 05 '19 at 20:25
  • 1
    Saying "it doesn't work" is useless. You need to explain what doesn't work. From the code posted I see many things wrong. Your trigger has a MAJOR flaw. It assumes you are only ever going to insert a single row. You also are not referencing the inserted virtual table. And you are trying add numbers to characters. – Sean Lange Nov 05 '19 at 20:25
  • The first thing you need to know about triggers in SQL Server is that they are not "per row" i.e. multiple rows could be being inserted/updated and your code must handle that. Sequences such as you desire must be updated a row at a time, so you are going to have to write a loop to handle each record that might be present in the `inserted` pseudo table. – Dale K Nov 05 '19 at 20:27
  • 1
    This won't work in multi-user environment because there is time gap between select and update. You would need to introduce locking to prevent race conditions. – Kamil Gosciminski Nov 05 '19 at 20:28
  • Then you need to do some research on generating and storing sequences, because its too large a topic for a single SO question. You can store the sequence in a separate table or within this table. But I would ensure you store the number separate from the string component and then combine them in a computed column. – Dale K Nov 05 '19 at 20:29
  • 1
    I would steer you away from trying to number each animal like this in the first place. What happens when you delete a row? Suddenly your numbering is all wrong. You would either have a gap or possibly have a new animal inserted with the same "UniqueID" as the last one that was deleted. This is an [XY Problem](http://xyproblem.info/) – Sean Lange Nov 05 '19 at 20:29
  • Thanks for the comments. I'm very new to SQL and have only had to query tables before. @DaleK that's correct I need the UniqueID to look like DeerM0001 and DeerF0001. – JNN Nov 05 '19 at 20:33
  • So you need to restart the numbering logic for both the species and the sex? I would very strongly urge you to reconsider this requirement. – Sean Lange Nov 05 '19 at 20:38
  • @SeanLange our database does not allow deleting rows so that's not a problem. – JNN Nov 05 '19 at 20:38
  • 2
    It doesn't today, but in the future it might. What you are trying to do here is violating 1NF because you are shoving three pieces of information into a single tuple. It is also why you are struggling with the logic. This is not how relational data is designed to work. – Sean Lange Nov 05 '19 at 20:41
  • @SeanLange what would you recommend then? Right now we fill in the Unique ID manually. The numbering logic needs to restart for both the species and sex. – JNN Nov 05 '19 at 20:47
  • 1
    I would use a guid or something else entirely. The design you have here is relying too heavily on this logic. If every number could be unique you could easily leverage a computed column and an identity. Or maybe you can generate a sequence for each species you can use. – Sean Lange Nov 05 '19 at 20:51
  • If you have to do it: 1) Create a [sequence table](https://stackoverflow.com/a/58329961/1127428) based on the AnimalSex combination. 2) Update your trigger to [loop through each row](https://stackoverflow.com/questions/5805413/how-can-i-get-a-trigger-to-fire-on-each-inserted-row-during-an-insert-into-table) in the `inserted` table allocating the UniqueID. (You probably don't need to handle updates assuming once a UniqueID is allocated you never change it). NOTE: I wouldn't use a cursor, I would use a table variable. – Dale K Nov 05 '19 at 21:00
  • It looks to me like you need to add an [identity column](https://stackoverflow.com/questions/10725705/how-to-create-table-with-identity-column) in any case. I also agree with the advice from @SeanLange – krubo Nov 05 '19 at 21:05

1 Answers1

0

Here is an example of using a computed column with an identity for this kind of thing. I would suggest that storing the Species as a string is not a great plan. You should have a Species table to avoid typing the name over and over. It prevents typos and also easily allows for multiple language support. Also, note that right padding with 0's like you have you have a built in limitation to the system. It will eventually run out of numbers.

create table Animal
(
    AnimalID int identity
    , Species varchar(10) --this should really be a foreign key instead of the name over and over
    , Sex char(1) not null
    , UniqueID as Concat(Species, Sex) + right(replicate('0', 8) + convert(varchar(10), AnimalID), 6) --this pads the identity value to a max length of 6.
)

insert Animal values
('Deer', 'M')
, ('Deer', 'M')
, ('Deer', 'M')
, ('Deer', 'F')
, ('Deer', 'F')
, ('Deer', 'F')
, ('Deer', 'F')
, ('Deer', 'F')
, ('Goat', 'F')
, ('Goat', 'F')
, ('Goat', 'M')
, ('Goat', 'M')
, ('Goat', 'M')
, ('Goat', 'M')

select *
from Animal

If you really want to use a sequence the basic syntax is this simple. 

create sequence MySequence as int start with 1 increment by 1

select next value for MySequence

You can find thousands of other examples here.

Sean Lange
  • 31,919
  • 3
  • 24
  • 38
  • I tried running your example but it doesn't restart the sequence for a female deer. Ex: Deer M is DeerM0000001 but Deer F is DeerF0000002. I need it to be DeerF0000001 because it is the first female deer. Definitely agree with adding a species table. – JNN Nov 05 '19 at 21:26
  • Right. As I said this won't restart the sequence (hint sequence is an object in sql server). This is a simple solution that doesn't require a lot of overhead. What you are describing will require a different sequence for each Species. And you will have to get the next value from the correct sequence every single insert. The type of numbering you are after is very common but is not easy to implement. – Sean Lange Nov 05 '19 at 22:06
  • Thanks. Would you be able to provide an example or a link of what that syntax for the sequence might look like? Really appreciate it. – JNN Nov 05 '19 at 22:13