MySQL Forums
Forum List  »  Triggers

pls solve this trigger, it has some errors- VII
Posted by: muneer ahmed
Date: July 26, 2006 11:59PM

table structure of pathway_stage_instance

CREATE TABLE `pathway_stage_instance` (
`title` varchar(100) NOT NULL,
`pathway_instance_id` bigint(20) unsigned NOT NULL default '0',
`status` tinyint(3) unsigned NOT NULL default '0',
`execute_date` date NOT NULL default '0000-00-00',
`id` bigint(20) NOT NULL auto_increment,
`day_sequence` int(11) NOT NULL,
`pathway_title` varchar(255) NOT NULL,
`updated_by` varchar(20) default NULL,
`updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `pathway_instance_id` (`pathway_instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


table structure of pathway_instance

CREATE TABLE `pathway_instance` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`visit_id` bigint(20) unsigned NOT NULL default '0',
`title` varchar(100) NOT NULL,
`updated_by` varchar(20) default NULL,
`updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`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 `PATHWAY_STAGE_INSTANCE_AU` AFTER UPDATE on `pathway_stage_instance`
for each row BEGIN

declare source varchar(255) default 'PATHWAY_STAGE_INSTANCE';
declare modification text default '';
declare visitId bigint(20);

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

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

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

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

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

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

select visit_id into visitId from pathway_instance as pathway where pathway.id = new.pathway_instance_id;

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



create trigger `PATHWAY_STAGE_INSTANCE_AI` AFTER INSERT on `pathway_stage_instance`
for each row BEGIN

declare source varchar(255) default 'PATHWAY_STAGE_INSTANCE';
declare modification text default '';
declare visitId bigint(20);

if(new.pathway_title is not null) then
set modification = concat(modification, 'pathway_title: ', new.pathway_title, ';');
end if;

if(new.day_sequence is not null) then
set modification = concat(modification, 'day_sequence: ', '/', new.day_sequence, ';');
end if;

if(new.execute_date is not null) then
set modification = concat(modification, 'execute_date: ', new.execute_date, ';');
end if;

if(new.status is not null) then
set modification = concat(modification, 'status: ', new.status, ';');
end if;

if(new.pathway_instance_id is not null) then
set modification = concat(modification, 'pathway_instance_id: ', new.pathway_instance_id, ';');
end if;

if(new.title is not null) then
set modification = concat(modification, 'title: ', new.title, ';');
end if;

select visit_id into visitId from pathway_instance as pathway where pathway.id = new.pathway_instance_id;
insert into audit_trial (visit_id,`user`,source,modification) values(visitId, new.updated_by, source, modification);
END;
$$

Options: ReplyQuote


Subject
Views
Written By
Posted
pls solve this trigger, it has some errors- VII
2410
July 26, 2006 11:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.