Add 1 to the field (collate from CSDN)

On CSDN (http://community.csdn.net/Expert/topic/4802/4802557.xml? Temp =. 4318659 ). The original question is ﹕
When the information is inserted, add 1 to a certain location, and Add 1 to each inserted location

Requirements:
Boat schedule sort: boat schedule
MS_0001
MS_0002 B
MS_0003 C
MS_0004 D
MS_0005 E
MS_0006 F
....

If the Division time is reached, the sequence of the positions can be deleted again: for example, the record of Ship D and E. Arranged as follows:
MS_0001
MS_0002 B
MS_0003 C
MS_0004 F
....

The answer to this questionLouisXIV)Give the following answer. I think it is very new, so I will put it here ﹕
-- Create an auto-Increment Function
Alter Function dbo. f_NextMS ()
Returns Char (7)
As
Begin
Return (Select 'Ms _ '+ RIGHT (100001 + IsNull (Right (Max (cqxh), 4), 0), 4) From cq With (Xlock, Paglock ))
End

-- Create a table. The cqxh column uses the default value of the auto-incrementing function.
Create table cq
(
Cqxh char (7) default dbo. f_NextMS (),
Ch varchar (10)
)

-- Insert data into tables

Insert into cq (ch)
Select 'A'
Go

Insert into cq (ch)
Select 'B'
Go

Insert into cq (ch)
Select 'C'
Go

Insert into cq (ch)
Select 'D'
Go

Insert into cq (ch)
Select 'D'
Go

Insert into cq (ch)
Select 'E'
Go

Insert into cq (ch)
Select 'F'
Go

-- Returns the result
Select * from cq

After division, the answer should adopt a producer. The new idea here is not very good, but it seems that the best method is lost.

Create trigger tr_MSDelete

On cq

For delete

As

-- Create an external table

Create table # temp

(

Id int identity (1, 1 ),

Ch varchar (10)

)

-- Move the deleted item into the current time table

Insert into # temp

Select ch

From table1

-- Clear the original table

Truncate table cq

-- Migrate the data in the current table back to the original table

Insert into table1

(Ch)

Select ch

From # temp

-- The missing point is that when the original table has a large amount of data, the incoming traffic generates a lot of data sources.

 

-- Zookeeper,

Select * from cq

Go

Delete from cq where ch in ('B', 'D ')

Go

Select * from cq

Go