pls solve this trigger, it has some errors-III

[muneer ahmed]

table strucuture of discharge_record

*Column Information For - cace_cdr_innodb.discharge_record*/
-------------------------------------------------------------

Field Type Collation Null Key Default Extra Privileges Comment
---------------------- ------------ --------------- ------ ------ ------------------- -------------- ------------------------------- ----------------------------------------------------------------
id bigint(20) (NULL) NO PRI (NULL) auto_increment select,insert,update,references primary key of discharge_record
visit_id bigint(20) (NULL) NO 0 select,insert,update,references foreign key to admit_info table
doctor varchar(20) utf8_general_ci NO 0 select,insert,update,references a user id of a doctor who discharged an inpatient
discharge_date_time datetime (NULL) NO 0000-00-00 00:00:00 select,insert,update,references a patient discharged date time
discharge_summary text utf8_general_ci YES (NULL) select,insert,update,references a summary about a patient when he is discharged made by a doctor
updated_by varchar(20) utf8_general_ci YES (NULL) select,insert,update,references a user id who updated discharge record of a patient
updated_on timestamp (NULL) YES CURRENT_TIMESTAMP select,insert,update,references timestamp when a patient discharged
rbc varchar(100) utf8_general_ci YES (NULL) select,insert,update,references rbc value of a patient discharge record
plasm varchar(100) utf8_general_ci YES (NULL) select,insert,update,references plasm value of a patient discharge record
respire_machine varchar(100) utf8_general_ci YES (NULL) select,insert,update,references respire_machine value of a patient discharge record
ecg varchar(100) utf8_general_ci YES (NULL) select,insert,update,references ecg value of a patient discharge record
deposition varchar(100) utf8_general_ci YES (NULL) select,insert,update,references deposition value of a patient discharge record
vital varchar(100) utf8_general_ci YES (NULL) select,insert,update,references vital value of a patient discharge record
hr varchar(100) utf8_general_ci YES (NULL) select,insert,update,references hr value of a patient discharge record
rr varchar(100) utf8_general_ci YES (NULL) select,insert,update,references rr value of a patient discharge record
bp varchar(100) utf8_general_ci YES (NULL) select,insert,update,references bp value of a patient discharge record
current_status varchar(100) utf8_general_ci YES (NULL) select,insert,update,references a short summary about a patient status
discharge_diagnosis varchar(100) utf8_general_ci YES (NULL) select,insert,update,references diagnosis of a patient when he is discharged
attention varchar(100) utf8_general_ci YES (NULL) select,insert,update,references attentions of a patient made by a doctor
actural_discharge_time datetime (NULL) YES (NULL) select,insert,update,references The actural datetime for a patient discharge

/*Index Information For - cace_cdr_innodb.discharge_record*/
------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
---------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
discharge_record 0 PRIMARY 1 id A 1 (NULL) (NULL) BTREE

/*DDL Information For - cace_cdr_innodb.discharge_record*/
----------------------------------------------------------

Table Create Table
---------------- -----------------------------------------------------------------------------------------------------------------------------------------
discharge_record CREATE TABLE `discharge_record` (
`id` bigint(20) NOT NULL auto_increment COMMENT 'primary key of discharge_record',
`visit_id` bigint(20) NOT NULL default '0' COMMENT 'foreign key to admit_info table',
`doctor` varchar(20) NOT NULL default '0' COMMENT 'a user id of a doctor who discharged an inpatient',
`discharge_date_time` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'a patient discharged date time',
`discharge_summary` text COMMENT 'a summary about a patient when he is discharged made by a doctor',
`updated_by` varchar(20) default NULL COMMENT 'a user id who updated discharge record of a patient',
`updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'timestamp when a patient discharged ',
`rbc` varchar(100) default NULL COMMENT 'rbc value of a patient discharge record',
`plasm` varchar(100) default NULL COMMENT 'plasm value of a patient discharge record',
`respire_machine` varchar(100) default NULL COMMENT 'respire_machine value of a patient discharge record',
`ecg` varchar(100) default NULL COMMENT 'ecg value of a patient discharge record',
`deposition` varchar(100) default NULL COMMENT 'deposition value of a patient discharge record',
`vital` varchar(100) default NULL COMMENT 'vital value of a patient discharge record',
`hr` varchar(100) default NULL COMMENT 'hr value of a patient discharge record',
`rr` varchar(100) default NULL COMMENT 'rr value of a patient discharge record',
`bp` varchar(100) default NULL COMMENT 'bp value of a patient discharge record',
`current_status` varchar(100) default NULL COMMENT 'a short summary about a patient status',
`discharge_diagnosis` varchar(100) default NULL COMMENT 'diagnosis of a patient when he is discharged',
`attention` varchar(100) default NULL COMMENT 'attentions of a patient made by a doctor',
`actural_discharge_time` datetime default NULL COMMENT 'The actural datetime for a patient discharge',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table structure of audit_trial

/*Column Information For - cace_cdr_innodb.audit_trial*/
--------------------------------------------------------

Field Type Collation Null Key Default Extra Privileges Comment
------------ ------------ --------------- ------ ------ ----------------- -------------- ------------------------------- -------
id bigint(20) (NULL) NO PRI (NULL) auto_increment select,insert,update,references
visit_id bigint(20) (NULL) YES (NULL) select,insert,update,references
user varchar(20) utf8_general_ci NO 0 select,insert,update,references
time timestamp (NULL) YES CURRENT_TIMESTAMP select,insert,update,references
source varchar(255) utf8_general_ci NO select,insert,update,references
modification text utf8_general_ci YES (NULL) select,insert,update,references

/*Index Information For - cace_cdr_innodb.audit_trial*/
-------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
----------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
audit_trial 0 PRIMARY 1 id A 7 (NULL) (NULL) BTREE

/*DDL Information For - cace_cdr_innodb.audit_trial*/
-----------------------------------------------------

Table Create Table
----------- ------------------------------------------------------------------------------------
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 `DISCHARGE_RECORD_AU` AFTER UPDATE on `discharge_record`
for each row BEGIN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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