Implementation of Automatic growth of ID fields based on a field in SQL Server

Create a table with different IDS Based on the description,

Requirements:

1. The administrator ID is auto-increment from 1000 and the auto-increment value is 1.

2. the ID of the department head increases from 2000 and the increment is 1.

3. the ID of an ordinary instructor increases from 3000 and the increment is 1.

The statement for creating a table is as follows:

create table C(   ID int,   Description varchar(50));

Create a trigger for the table as follows:

 

Create trigger tr_syiton C for insertas declare @ R1 int, @ C1 int, @ N1 int, @ R2 int, @ C2 int, @ N2 int, @ R3 int, @ C3 int, @ N3 int -- Process administrator number select @ n1 = inserted. ID from inserted; select @ C1 = count (ID) from C where description = 'admin'; If (@ C1 = 1) set @ R1 = 1000; else begin select @ R1 = max (ID) from C where description = 'admin'; Set @ R1 = @ R1 + 1; end -- Process head No. Select @ n2 = inserted. ID from inserted; select @ C2 = count (ID) from C where description = 'department head '; If (@ C2 = 1) set @ r2 = 2000; else begin select @ r2 = max (ID) from C where description = 'department head '; Set @ r2 = @ r2 + 1; end -- Process General instructor number select @ N3 = inserted. ID from inserted; select @ C3 = count (ID) from C where description = 'normal instructor '; If (@ C3 = 1) set @ R3 = 3000; else begin select @ R3 = max (ID) from C where description = 'normal instructor '; Set @ R3 = @ R3 + 1; end -- execute the insert operation update C set id = (case when description = 'admin' then @ R1 when description = 'header' then @ R2 else @ R3 end) where id = (case when description = 'post' then @ N1 when description = 'head of the Line' then @ N2 else @ N3 end ); -- insert into C values (1, 'postmaster ') for testing; insert into C values (1, 'department head'); insert into C values (1, 'normal instructor '); -- query data select * from C;

The result is as follows: insert three rows of data.