10 years Hedong, 10 years hexi, MO bullying Juvenile poor
Lifelong Learning, Excellence
Today is the code of the database scripting class, so I don't want to elaborate too much
The following data table:
create table card (Id int identity (1 , 1 ) primary key cardno varchar (50 ) unique not null , Status bit default (0
The table above has a self-growing field ID, which now requires the field Cardno and the self-growth ID to be associated, for example, when the ID is 1 o'clock, Cardno should be Card00001, when the ID is 15 o'clock, Cardno should be: Card00015, required by a trigger or stored procedure implementation?
Where the trigger is implemented as follows:
Create TRIGGER [dbo].[Tgr_card] on [dbo].[Card] AfterINSERT as BEGIN --SET NOCOUNT on added to prevent extra result sets from --interfering with SELECT statements. SETNOCOUNT on; --Insert statements for trigger here, DECLARE @Id bigint SELECT @Id =Id fromINSERTEDUPDATECardSETCardno= Case when LEN(@Id)= 1 Then 'Crad0000000' + CAST(@Id as varchar( -)) when LEN(@Id)= 2 Then 'Crad000000' + CAST(@Id as varchar( -)) when LEN(@Id)= 3 Then 'Crad00000' + CAST(@Id as varchar( -)) when LEN(@Id)= 4 Then 'Crad0000' + CAST(@Id as varchar( -)) when LEN(@Id)= 5 Then 'Crad000' + CAST(@Id as varchar( -)) when LEN(@Id)= 6 Then 'Crad00' + CAST(@Id as varchar( -)) when LEN(@Id)= 7 Then 'Crad0' + CAST(@Id as varchar( -)) when LEN(@Id)= 8 Then 'Crad' + CAST(@Id as varchar( -)) END WHEREOrderId= @Id END
What if the stored procedure is implemented?
The stored procedure implementation needs to consider the following factors:
1. Stored procedure requires program call execution
2, if the same time multiple data inserted into the database, you should consider the data concurrency, therefore, the adoption of the stored procedure implementation should avoid concurrency
So, how should stored procedures be implemented?
We all know, prevent concurrent we adopt optimistic lock or pessimistic lock, do not demonstrate here, in short, to add Version field, database type: timestamp!
For the avoidance of concurrency, please refer to: http://www.cnblogs.com/chenwolong/p/Lock.html
Http://www.cnblogs.com/chenwolong/p/BF.html
@ Chen Wolong's blog
Using database triggers to correlate fields with self-growing IDs