Using database triggers to correlate fields with self-growing IDs

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