How to update status with message wrong sequnce for all numbers where there are numbers remaining or down on sequence ?

Question

I work on SQL server2012 I face issue I can't stop insert data on group table when Numbers not complete sequence or have numbers not exist or down .

as example

seqManual are 1,2,3,4 per only groupseq B so that Insert data success to table groups .

but if sequence are 1,2,4,5,7 so that not inserted because it have some numbers down or not exist on sequence

so i will update status by message wrong sequence

How to Update status per all Numbers where it have some numbers down or not exist on sequence ?
and not insert it .

create table #groupsseq
(
groupseq nvarchar(20),
SeqManual int,
status nvarchar(100)
)
insert into #groupsseq(groupseq,SeqManual)
values
('A',1),
('A',2),
('A',4),
('A',5)

create table #Groups
(
groupName nvarchar(20),
SeqManual int
)

Expected Result

groupseq    SeqManual   Status
A   1   Wrong Sequence
A   2   Wrong Sequence
A   4   Wrong Sequence
A   5   Wrong Sequence

but if 1,2,3,4 and groupseq B
so result will be

groupseq SeqManual Status
B 1 success
B 2 success
B 3 success
B 4 success

So How to do that please ?

Answer 1

You could do this with a trigger except you are not allowed to create a trigger on a temporary table. For a temporary table, you will need an update statement after the insert, like

create table #groupsseq
(
groupseq nvarchar(20),
SeqManual int,
status nvarchar(100)
)
insert into #groupsseq(groupseq,SeqManual)
values
('A',1),
('A',2),
('A',4),
('A',5),
('B',1),
('B',2),
('B',3),
('B',4);

;With cte As
(Select groupseq, SeqManual, status, 
  Case When Min(SeqManual) Over(Partition By groupseq) <> 1
   Or Max(SeqManual) Over(Partition By groupseq) - Min(SeqManual) Over(Partition By groupseq) <> Count(SeqManual) Over(Partition By groupseq) - 1
   Then 'Wrong Sequence' Else 'success' End As NewStatus
From #groupsseq)
Update cte
Set status = NewStatus;

Select * From #groupsseq Order By groupseq, SeqManual;

Tom