Using database triggers to correlate fields with self-growing IDs

Source: Internet
Author: User

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

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.