-1

Is it possible to do a sequence with Start at 1 and INCREMENT 1, and when changing the year, restart the sequence?

-- code# Create a sequence  
 CREATE SEQUENCE SEQ_ALUNO
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 10
 CYCLE  CACHE 10;

-- code# Create a table T_ALUNO  
 CREATE TABLE T_ALUNO 
 (
 COD_ALUNO INT,
 NOM_ALUNO VARCHAR(50),
 ANO int 
 );

Note: I am using SQL server express and will not be able to use the windows task scheduler.

  • 1
    How many stored procedures or pieces of code directly cause the sequence to increment? – Aaron Bertrand Aug 9 at 19:39
  • I did not understand. When entering a record in the table above, create a sequence in the COD_ALUNO column and when the year changes, the sequence will restart. – Edvaldo Lucena Aug 9 at 19:47
1

I don't think it is possible using a Sequence. Sequence use cycles between numbers like bigint, and cycle to either the min or max depending if its an ascending or descending sequence.

This problem can be solved by using a Trigger on Insert which performs an update to the field you aim to have a cyclic sequence number based on ANO(Year).

First create the table:

--Drop and Create table T_ALUNO  
IF OBJECT_ID('T_ALUNO','U') IS NOT NULL
DROP TABLE T_ALUNO

 CREATE TABLE T_ALUNO 
 (
     Id int not null identity(1,1), -- add id for easy joining after insert
     COD_ALUNO INT default 0, --added a default for primary key 
     NOM_ALUNO VARCHAR(50), 
     ANO int,
     Primary Key (COD_ALUNO, NOM_ALUNO, ANO) -- added for uniqueness
 )

Second, Create a Trigger on Insert to perform an update which will include a common table expression (cte) including a Window Function Row_Number() with a partition as well as an ordering clause. The ordering here is crucial and specific to the default value chosen (0).

Then the update uses this new row number field value in a join on the inserted table by our new id field. The inserted table is a reference table like the deleted table which contains rows of the same schema from the current operation - in our case insert.

CREATE OR ALTER TRIGGER tr_Sequence_Annual_Cycle 
ON T_ALUNO
AFTER INSERT
AS
;with mycte as (
select row_number() over (partition by ANO order by case when id = 0 then 1 else 0 end, id) rnum 
,id 
,COD_ALUNO
from T_ALUNO 
)

UPDATE m
set COD_ALUNO = m.rnum
FROM mycte m
inner join inserted i 
on m.Id = i.Id

Finally perform a test of the newly created trigger by inserting some test values:

insert into T_ALUNO (NOM_ALUNO,ANO)
 select 'JOSE', 2019
 union all
select 'JAVIER', 2019
 union all
select 'JUANITA', 2019
 union all
select 'NOE', 2020
 union all
select 'JUAQUINA', 2021

select * from T_ALUNO order by 4,2

insert into T_ALUNO (
 NOM_ALUNO,
 ANO)
 select 'JOSEPH', 2019
 union all
select 'JESSE', 2019
 union all
select 'JOHN', 2019
 union all
select 'JIM', 2020
 union all
select 'JOAN', 2021

select * from T_ALUNO order by 4,2

And you should see the following results:

Query Result

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.