mysql个别字段无法保存_mysql使用触发器解决冗余字段未保存的问题

drop TRIGGER if EXISTS auto_set_value;

CREATE TRIGGER auto_set_value BEFORE INSERT ON t_trans_surgery_info FOR EACH ROW

begin

SETnew.patient_id =(

SELECT

p.patient_id

FROM

t_trans_patient p

LEFT JOIN t_trans_seek_medical m ON p.patient_id=m.patient_id

WHERE

m.medical_id= new.medical_id

);

SETnew.his_patient_id =(

SELECT

p.his_patient_id

FROM

t_trans_patient p

LEFT JOIN t_trans_seek_medical m ON p.patient_id=m.patient_id

WHERE

m.medical_id= new.medical_id

);

SETnew.patient_name =(

SELECT

p.patient_name

FROM

t_trans_patient p

LEFT JOIN t_trans_seek_medical m ON p.patient_id=m.patient_id

WHERE

m.medical_id= new.medical_id

);

SETnew.patient_pym =(

SELECT

p.pym

FROM

t_trans_patient p

LEFT JOIN t_trans_seek_medical m ON p.patient_id=m.patient_id

WHERE

m.medical_id= new.medical_id

);

SETnew.patient_type =(

SELECT

m.patient_type

FROM

t_trans_seek_medical m

WHERE

m.medical_id= new.medical_id

);

SETnew.opt_room_name =(

SELECT

r.room_name

FROM

t_base_operation_room r

WHERE

r.opt_room_id= new.opt_room_id

);

SETnew.opt_room_pym =(

SELECT

r.pym

FROM

t_base_operation_room r

WHERE

r.opt_room_id= new.opt_room_id

);

SETnew.opt_room_no =(

SELECT

r.room_no

FROM

t_base_operation_room r

WHERE

r.opt_room_id= new.opt_room_id

);

SETnew.dept_pym =(

SELECT

d.pym

FROM

t_base_dept d

WHERE

d.dept_id= new.dept_id

);

SETnew.dept_name =(

SELECT

d.dept_name

FROM

t_base_dept d

WHERE

d.dept_id= new.dept_id

);

end