mysql - How to create a trigger the triggers a self-referencing trigger...yeah -

mysql - How to create a trigger the triggers a self-referencing trigger...yeah -

i have mysql database with...interesting architecture , convoluted enrollment process. there several programme tables need insert rows on historic enrollment table when they're updated, inserted or deleted. i've got working using triggers on each of tables (around 30 different tables) using next iterated on of tables:

drop trigger if exists programtable_afterinsert;$$ create trigger programtable_afterinsert after insert on programtable each row begin if new.enrolled = 1 insert enrollment (id, action, date_updated, user, program, reason, action_date) values (new.id, 'enrolled', now(), 'programuser', 'programname', 'enrolled in program', now()); elseif new.enrolled = 0 insert enrollment (member_id, action, date_updated, user, program, reason, action_date) values (new.id, 'disenrolled', now(), 'programuser', 'programname', 'disenrolled program', now()); end if; end;$$ drop trigger if exists programtable_afterupdate;$$ create trigger programtable_afterupdate after update on programtable each row begin if new.enrolled = 1 insert enrollment (id, action, date_updated, user, program, reason, action_date) values (new.member_id, 'enrolled', now(), 'programuser', 'programname', 'enrolled in program', now()); elseif new.enrolled = 0 insert enrollment (id, action, date_updated, user, program, reason, action_date) values (new.id, 'disenrolled', now(), 'programuser', 'programname', 'disenrolled program', now()); end if; end;$$ drop trigger if exists programtable_afterdelete;$$ create trigger programtable_afterdelete after delete on programtable each row begin if old.enrolled = 1 insert enrollment (id, action, date_updated, user, program, reason, action_date) values (old.id, 'disenrolled', now(), 'programuser', 'programname', 'removed program', now()); end if; end;$$

a stripped downwards version of enrollment , programme tables can created following:

delimiter $$ create table `programtable1` ( `id` varchar(15) not null, `enrolled` tinyint(3) unsigned not null, `referral_date` datetime not null, `referral_source` varchar(255) );$$ create table `programtable2` ( `id` varchar(15) not null, `enrolled` tinyint(3) unsigned not null, `referral_date` datetime not null, `referral_source` varchar(255) );$$ create table `enrollment` ( `id` varchar(15) not null, `action` varchar(12) not null, `date_updated` timestamp not null, `user` varchar(12) default null, `program` varchar(25) not null, `notes` varchar(100) default null, `reason` varchar(45) default null, `action_date` datetime not null );$$

the hurdle i'm running enrollment table needs update programme tables' enrollment when it's modified or row added it. meaning if enrolled on programme table, need have entry on enrollment table action; if enrolled via enrollment table, need updated enrolled or disenrolled on programme table row applies to.

the main problem there 2 different sources people enrolling in programs from.

like said, convoluted. know architecture of application isn't best, it's not can changed.

any ideas welcome! please allow me know if there has questions or if clarification needed. i've been working on awhile now, know i'm leaving stuff out of equation due beingness familiar it.

there's no problem creating triggers update each others' tables -- long triggers don't go on insert , forth in infinite loop.

you need create sure insert performed trigger inserts row other table not result in reciprocal action.

i wrote illustration in recent question: mirror tables: triggers, deadlock , implicit commits

mysql stored-procedures dynamic triggers