For example: I upload a contract for the employee "NVP\d.vanleeuwen". The uploaded file should be stored in the following file path: \\fileserver\file_upload\Employee\d.vanleeuwen\contract.pdf
In this case the path must be created as follows: \\fileserver\file_upload\Employee\@variable_employee_name\@uploaded_file
I've created a domain with control_id "File upload" and a file_storage_id who is linked to a storage location on our file-server (For example: \\fileserver\file_upload\Employee\).
I've already tried to write and executed the following code for the insert_trigger of the employee_contract table. Unfortunately this doesn't work because it changes the path but does not move the file.
code:
-- parameters declaren
declare @user_name varchar(100)
,@path varchar(200)
,@file varchar(200)
-- user name ophalen en strippen van prefix
select @user_name = replace(e.employee_user_name,'nvp\','')
from employee e
join inserted i
on i.employee_id = e.employee_id
-- path is vast gedefinieerd
set @path = '\\fileserver\file_upload\Employee\'
-- file geupload is variabel, bestandsnaam ophalen.
select @file = replace(ec.contract_file,'\\fileserver\file_upload\Employee','')
from employee_contract ec
join inserted i
on i.employee_contract_id = ec.employee_contract_id
-- alle variabelen samenvoegen tot nieuwe pad naam
update ec
set contract_file = concat(@path
,@user_name
,@file
)
from employee_contract ec
join inserted i
on i.employee_contract_id = ec.employee_contract_id
I am looking forward to a possible solution đŸ’¡
