Is there a way that i can create an after update trigger that will detect if there is a corresponding record with a 999 prefix and update that record?
Try something like this in your trigger
set AccountName = ins.AccountName
, BankBuildingSocietyName = ins.BankBuildingSocietyName
, BankAddress = ins.BankAddress
, BankPostcode = ins.BankPostcode
, BankSortCode = ins.BankSortCode
, BankAccountNumber = ins.BankAccountNumber
, RollRefNumber = ins.RollRefNumber
from BankDtails bd
inner join inserted ins on db.ProjectID = convert(int,'999' + convert(varchar,ins.ProjectID))
I'm assuming that projectid is an int, if not you won't need the convert statements in the join.