This is to note that currently - as @ version 5.6 of MySQL there is a severe non ACID compliant limitation where triggers are not run during a foreign key action e.g. cascade delete.
This has significance to us where orphans can be created (resource_data, and duty_data) delete a project, cascade deletes tasks, cascade deletes duties, however the trigger on delete duty does not fire and hence if 0 related left to duty_data the duty_data is orphaned.
Current work around is to use triggers instead of cascade where this is significant meaning triggers on task, crew, day, project and possibly planning to handle cascaded deletes as opposed to foreign key action of cascade delete.
Will need to check schema to make sure no more issues with this.
This is to note that currently - as @ version 5.6 of MySQL there is a severe non ACID compliant limitation where triggers are not run during a foreign key action e.g. cascade delete.
This has significance to us where orphans can be created (resource_data, and duty_data) delete a project, cascade deletes tasks, cascade deletes duties, however the trigger on delete duty does not fire and hence if 0 related left to duty_data the duty_data is orphaned.
Current work around is to use triggers instead of cascade where this is significant meaning triggers on task, crew, day, project and possibly planning to handle cascaded deletes as opposed to foreign key action of cascade delete.
Will need to check schema to make sure no more issues with this.
@andrew once complete then close.