Database Field

Results 1 to 2 of 2

Thread: Database Field

  1. #1
    Join Date
    Dec 1969

    Default Database Field

    I have a database field: Ref_Code (not a primary key)<BR>If the ref_code is Q234 how can I automatically increment this by one every time I add a new record.<BR>For example if i add a new record ref_code becomes Q235.<BR>Any suggestions!

  2. #2
    Join Date
    Dec 1969

    Default RE: Database Field

    Use a trigger on this table.<BR>Something like this:<BR><BR>CREATE TRIGGER [trg_insert_RefCode] ON dbo.MyTable<BR>FOR INSERT<BR>AS<BR>DECLARE @PK int, @NewRefCode char(4)<BR><BR>DECLARE inserted_records CURSOR FOR SELECT Ref_Code FROM inserted<BR>OPEN inserted_records<BR>FETCH NEXT FROM inserted_records INTO @PK<BR>WHILE @@FETCH_STATUS = 0<BR>BEGIN<BR> SELECT @NewRefCode = &#039;Q&#039; + (MAX( RIGHT(RefCode, 3) ),0) + 1) FROM ModuleList WHERE PK = @PK<BR> UPDATE MyTable SET Ref_Code = @RefCode WHERE ModuleID = @new_ModuleID<BR> FETCH NEXT FROM inserted_records INTO @RefCode<BR>END<BR>CLOSE inserted_records<BR>DEALLOCATE inserted_records<BR><BR><BR><BR>I didn&#039;t test this, the part where you maken &#039;Q235&#039; from &#039;Q234&#039; may not be correct, but this should help you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts