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;
When placed in a file and used with the 'source' command, produces the following errors...
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int' at line 4
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare l_job_number varchar(45)' at line 1
ERROR 1327 (42000): Undeclared variable: l_job_number
ERROR 1327 (42000): Undeclared variable: l_current_max
ERROR 1193 (HY000): Unknown system variable 'sample_number'
ERROR 1054 (42S22): Unknown column 'NEW.sample_id' in 'field list'
ERROR 1109 (42S02): Unknown table 'NEW' in field list
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end' at line 1