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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.