pls solve this trigger, it has some errors-ix

[muneer ahmed]

table structure of patient_info

CREATE TABLE `patient_info` (
`patient_id` bigint(20) NOT NULL auto_increment COMMENT 'primary key',
`current_visit_id` bigint(20) default NULL COMMENT 'foreign key to admit_info table',
`MRN` varchar(100) NOT NULL COMMENT 'medical record number of a patient',
`first_name` varchar(100) NOT NULL COMMENT 'a patient first name',
`middle_name` varchar(100) default NULL COMMENT 'a patient middle name',
`last_name` varchar(100) NOT NULL COMMENT 'a patient last name',
`maiden_name` varchar(100) default NULL COMMENT 'a patient maiden name',
`gender` varchar(100) NOT NULL COMMENT 'a patient gender',
`DOB` date NOT NULL default '0000-00-00' COMMENT 'date of birth of a patient',
`blood_type` varchar(100) default NULL COMMENT 'blood type of a patient',
`nation_id` varchar(100) default NULL COMMENT 'a id indicates a patient nation . it linked to a nation dictory in HL7',
`email` varchar(255) default NULL COMMENT 'an email address of a patient',
`occupation` varchar(255) default NULL COMMENT 'a patient occupation',
`education` varchar(255) default NULL COMMENT 'an education level of a patient',
`address_contact` varchar(255) default NULL COMMENT 'an address of a patient',
`phone_home` varchar(100) default NULL COMMENT 'a home phone number of a patient',
`phone_business` varchar(100) default NULL COMMENT 'a business phone number of a patient',
`marital_status` varchar(100) default NULL COMMENT 'a marital status of a patient',
`update_by` varchar(20) default NULL COMMENT 'a user id who updated this patient record',
`update_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'TIMESTAMP when the patient record is updated',
PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table structure of audit_trial

CREATE TABLE `audit_trial` (
`id` bigint(20) NOT NULL auto_increment,
`visit_id` bigint(20) default NULL,
`user` varchar(20) NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`source` varchar(255) NOT NULL default '',
`modification` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create trigger `PATIENT_INFO_AU` AFTER UPDATE on `patient_info`
for each row BEGIN

declare source varchar(255) default 'PATIENT_INFO';
declare modification text default '';

if(new.patient_id is null AND old.patient_id is not null) then
set modification = concat(modification, 'patient_id: ', old.patient_id, '/', ';');
elseif(new.patient_id is not null AND old.patient_id is null) then
set modification = concat(modification, 'patient_id: ', '/', new.patient_id, ';');
elseif(new.patient_id != old.patient_id) then
set modification = concat(modification, 'patient_id: ', old.patient_id, '/', new.patient_id, ';');
end if;

if(new.MRN is null AND old.MRN is not null) then
set modification = concat(modification, 'MRN: ', old.MRN, '/', ';');
elseif(new.MRN is not null AND old.MRN is null) then
set modification = concat(modification, 'MRN: ', '/', new.MRN, ';');
elseif(new.MRN != old.MRN) then
set modification = concat(modification, 'MRN: ', old.MRN, '/', new.MRN, ';');
end if;

if(new.first_name is null AND old.first_name is not null) then
set modification = concat(modification, 'first_name: ', old.first_name, '/', ';');
elseif(new.first_name is not null AND old.first_name is null) then
set modification = concat(modification, 'first_name: ', '/', new.first_name, ';');
elseif(new.first_name != old.first_name) then
set modification = concat(modification, 'first_name: ', old.first_name, '/', new.first_name, ';');
end if;

if(new.middle_name is null AND old.middle_name is not null) then
set modification = concat(modification, 'middle_name: ', old.middle_name, '/', ';');
elseif(new.middle_name is not null AND old.middle_name is null) then
set modification = concat(modification, 'middle_name: ', '/', new.middle_name, ';');
elseif(new.middle_name != old.middle_name) then
set modification = concat(modification, 'middle_name: ', old.middle_name, '/', new.middle_name, ';');
end if;

if(new.last_name is null AND old.last_name is not null) then
set modification = concat(modification, 'last_name: ', old.last_name, '/', ';');
elseif(new.last_name is not null AND old.last_name is null) then
set modification = concat(modification, 'last_name: ', '/', new.last_name, ';');
elseif(new.last_name != old.last_name) then
set modification = concat(modification, 'last_name: ', old.last_name, '/', new.last_name, ';');
end if;

if(new.maiden_name is null AND old.maiden_name is not null) then
set modification = concat(modification, 'maiden_name: ', old.maiden_name, '/', ';');
elseif(new.maiden_name is not null AND old.maiden_name is null) then
set modification = concat(modification, 'maiden_name: ', '/', new.maiden_name, ';');
elseif(new.maiden_name != old.maiden_name) then
set modification = concat(modification, 'maiden_name: ', old.maiden_name, '/', new.maiden_name, ';');
end if;

if(new.gender is null AND old.gender is not null) then
set modification = concat(modification, 'gender: ', old.gender, '/', ';');
elseif(new.gender is not null AND old.gender is null) then
set modification = concat(modification, 'gender: ', '/', new.gender, ';');
elseif(new.gender != old.gender) then
set modification = concat(modification, 'gender: ', old.gender, '/', new.gender, ';');
end if;

if(new.DOB is null AND old.DOB is not null) then
set modification = concat(modification, 'DOB: ', old.DOB, '/', ';');
elseif(new.DOB is not null AND old.DOB is null) then
set modification = concat(modification, 'DOB: ', '/', new.DOB, ';');
elseif(new.DOB != old.DOB) then
set modification = concat(modification, 'DOB: ', old.DOB, '/', new.DOB, ';');
end if;

if(new.blood_type is null AND old.blood_type is not null) then
set modification = concat(modification, 'blood_type: ', old.blood_type, '/', ';');
elseif(new.blood_type is not null AND old.blood_type is null) then
set modification = concat(modification, 'blood_type: ', '/', new.blood_type, ';');
elseif(new.blood_type != old.blood_type) then
set modification = concat(modification, 'blood_type: ', old.blood_type, '/', new.blood_type, ';');
end if;

if(new.nation_id is null AND old.nation_id is not null) then
set modification = concat(modification, 'nation_id: ', old.nation_id, '/', ';');
elseif(new.nation_id is not null AND old.nation_id is null) then
set modification = concat(modification, 'nation_id: ', '/', new.nation_id, ';');
elseif(new.nation_id != old.nation_id) then
set modification = concat(modification, 'nation_id: ', old.nation_id, '/', new.nation_id, ';');
end if;

if(new.email is null AND old.email is not null) then
set modification = concat(modification, 'email: ', old.email, '/', ';');
elseif(new.email is not null AND old.email is null) then
set modification = concat(modification, 'email: ', '/', new.email, ';');
elseif(new.email != old.email) then
set modification = concat(modification, 'email: ', old.email, '/', new.email, ';');
end if;

if(new.occupation is null AND old.occupation is not null) then
set modification = concat(modification, 'occupation: ', old.occupation, '/', ';');
elseif(new.occupation is not null AND old.occupation is null) then
set modification = concat(modification, 'occupation: ', '/', new.occupation, ';');
elseif(new.occupation != old.occupation) then
set modification = concat(modification, 'occupation: ', old.occupation, '/', new.occupation, ';');
end if;

if(new.education is null AND old.education is not null) then
set modification = concat(modification, 'education: ', old.education, '/', ';');
elseif(new.education is not null AND old.education is null) then
set modification = concat(modification, 'education: ', '/', new.education, ';');
elseif(new.education != old.education) then
set modification = concat(modification, 'education: ', old.education, '/', new.education, ';');
end if;

if(new.address_contact is null AND old.address_contact is not null) then
set modification = concat(modification, 'address_contact: ', old.address_contact, '/', ';');
elseif(new.address_contact is not null AND old.address_contact is null) then
set modification = concat(modification, 'address_contact: ', '/', new.address_contact, ';');
elseif(new.address_contact != old.address_contact) then
set modification = concat(modification, 'address_contact: ', old.address_contact, '/', new.address_contact, ';');
end if;

if(new.phone_home is null AND old.phone_home is not null) then
set modification = concat(modification, 'phone_home: ', old.phone_home, '/', ';');
elseif(new.phone_home is not null AND old.phone_home is null) then
set modification = concat(modification, 'phone_home: ', '/', new.phone_home, ';');
elseif(new.phone_home != old.phone_home) then
set modification = concat(modification, 'phone_home: ', old.phone_home, '/', new.phone_home, ';');
end if;

if(new.phone_business is null AND old.phone_business is not null) then
set modification = concat(modification, 'phone_business: ', old.phone_business, '/', ';');
elseif(new.phone_business is not null AND old.phone_business is null) then
set modification = concat(modification, 'phone_business: ', '/', new.phone_business, ';');
elseif(new.phone_business != old.phone_business) then
set modification = concat(modification, 'phone_business: ', old.phone_business, '/', new.phone_business, ';');
end if;

if(new.marital_status is null AND old.marital_status is not null) then
set modification = concat(modification, 'marital_status: ', old.marital_status, '/', ';');
elseif(new.marital_status is not null AND old.marital_status is null) then
set modification = concat(modification, 'marital_status: ', '/', new.marital_status, ';');
elseif(new.marital_status != old.marital_status) then
set modification = concat(modification, 'marital_status: ', old.marital_status, '/', new.marital_status, ';');
end if;

insert into audit_trial (visit_id,`user`,source,modification) values(new.current_visit_id, new.update_by, source, modification);
END;