pls solve this trigger, it has some errors- VII
Posted by: muneer ahmed
Date: July 26, 2006 11:59PM
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;
$$
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;
$$
Subject
Views
Written By
Posted
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.