Untitled

create trigger t_samples_ib before insert on asb_samples
	for each row
		begin
			declare l_current_max int unsigned;
			declare l_job_number varchar(45);
			select job_number from asb_jobs where job_id = NEW.job_id into l_job_number;
			select count(*) + 1 from asb_samples where job_id = NEW.job_id into l_current_max;
			set NEW.sample_number = concat_ws('-', l_job_number, l_current_max);
			insert into asb_sample_custody (sample_id, custodian, custody_date) values (NEW.sample_id, NEW.sampler_id, now());
			call update_job(NEW.job_id);
		end;