Trigger after a record is insertd to a table

1

I've the following table: StockCode, BinNumber, Date, Hold Status, Source
Once a new record is added I mean new StockCode. I need update this StockCode which is also a primary Key by adding random digit number up front. Only one field needs to be updated. The update is only required if transaction Source = 'P'.
For example if a recors with stock code A100 is inserted I want to change it with for example 1234.A100
Your help is greatly appreciated.

Thanks You

2

I would do this...
Actually I wouldn't do this as I suspect it's due to something dubious about the system design.
But probably better to use an instead of trigger which will intercept the insert.
But for an after trigger something like

create trigger tr_a on a for insert
as
update a
set s = convert(varchar(4),convert(int,rand()*10000)) + '.' + i.StockCode
from a a
join inserted i
on a.StockCode = i.StockCode
where i.TransactionSource = 'P'
go

Note the random number won't be unique so this could give errors - you could get another random number before the update if that happens.
Another option
Change the table to have an identity and Include the identity in the primary key.
Create another filtered index to guarantee uniqueness of StockCode for those without transaction source = 'P'

3

this is a very unusual requirement? why do you need to do this on a primary key?