SQL Update a column in a table using Trigger to a Function

Question

I am trying to auto generate a document reference number when a new document is added to an sql table - the reference number is a concatonation of some of the other fields in that table.

Looking online i can see one method is to use the dbid function to generate a uid and then create a function to concatonate and then a trigger on the table on insert to populate the column, but ive spent numerous hours and i cant get it to work.

The table has the following columns:-

Table:-     dbo.codeallocations21322        

Columns :-
Dbid        
Projectcode 
Type        
Discipline]     
Hdlreference

So the hdlreference column would be populated with :-

[projectcode]-[type]-[discipline]-[bdid]

With the [bdid] set to 6 characters'.

Eg 21322-rfq-mech-000001 Any help would be much appreciated / advise a better way ?

Many thanks in advance.

I assume SQL-Server because dbo.. Please tag your DBMS in future — JohnHC
– JohnHC, Commented Mar 30, 2017 at 14:34
Thanks for the feedback @JohnHC - will heed your advice, first post. Cheers. — Andy Johnston
– Andy Johnston, Commented Mar 30, 2017 at 14:51

JohnHC · Accepted Answer · 2017-03-30 15:19:07Z

1

You can use a computed column

alter table codeallocations21322
  add hdlreference 
      as ( projectcode  + '-' + type + '-' + discipline + '-' + dbid);

answered Mar 30, 2017 at 15:19

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andy Johnston Over a year ago

Many thanks for that answer @JohnHC - altered it slightly to convert the dbID to a Varchar :-

Andy Johnston · Accepted Answer · 2017-03-30 15:52:31Z

0

Many thanks John - altered slightly to convert the Int dbID to a varchar and works great :-

alter table CodeAllocations21322

  add hdlreference2

    as (ProjectCode  + '-' + Type + '-' + Discipline + '-' + right('00000' + Cast (dbID AS varchar(5)), 5));

Thanks again, look forward to talking to you in the future.

answered Mar 30, 2017 at 15:52

Andy Johnston

113 bronze badges

Collectives™ on Stack Overflow

SQL Update a column in a table using Trigger to a Function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related