SQL Server development using triggers for automatic numbering

server| triggers use SQL Server to create fields of numeric types, which can be set to automatic numbering. But many times it does not meet our needs, such as the number of students, may need to use the grade, department, etc. plus serial number. Here is a simple example of using triggers to automatically number. --Create a test table if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Usertable] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
drop table [dbo]. [Usertable]
Go CREATE TABLE usertable (userid varchar (), username nvarchar (20))
Go-Create triggers
Create Trigger Tg_insert on usertable
For insert
As
declare @username nvarchar (20)
DECLARE @userid varchar (20)
DECLARE @num int
DECLARE @strNum varchar (20)
DECLARE @prefix varchar (10)
DECLARE @Numlen int
DECLARE @strDate varchar (20)
--Get Current date
Set @strDate =substring (CONVERT (varchar), GETDATE (), 112), 1,8)
--Set the length of the serial number
Set @Numlen = 4
--Set Prefix
Set @prefix = ' S '
Select @userid =max (userid) from usertable
where UserID like @prefix + @strDate + '% '
If @userid is null
Set @num =0
Else
Set @num =cast (replace (@userid, @prefix + @strDate, ') as int)
Set @num = @num + 1
Set @strNum = cast (@num as varchar (10))
while (Len (@strNum) < @Numlen)
Set @strNum = ' 0 ' + @strNum
Set @userid = @prefix + @strDate + @strNum
Select @username =username from inserted
Rollback
INSERT into usertable values (@userid, @username) Go--Test insert INTO usertable (username) v Alues (' AA ')
Go
INSERT into usertable (username) values (' BB ')
Go
INSERT into usertable (username) VALUES (' CC ')
Go--Display data
SELECT * FROM Usertable
Go