Bandito Bandito - 8 months ago 39
MySQL Question

Add a global rule to modify dates for all database inserts & updates

I have a requirement to add a rule to a legacy MySQL 5.1.73 database whenever a specified date is about to be inserted or updated into any of the tables. (Each field already had a default date setting so using that in some way is not a viable solution).

For example:

IF NewDate = *TheSpecifiedDate* THEN SET NewDate = *ConstantDate*

The logic for this will be identical for all tables that have one or more DateTime fields.

My only solution at the moment is manually add triggers to each and every table. This will be a lot of work to do, and a lot of hassle to maintain if the requirement ever changes.

I therefore wondered if I can somehow make this a globalized rule/trigger for the entire database whenever an insert or update is attempted on any DateTime field?

Or is there a more elegant/preferred way of implementing this kind of global rule that may not even involve using triggers?

Answer Source

Just to close this off, for anyone trying to do something similar...

The main restriction in my scenario was that I am slowly migrating 200+ legacy applications and could therefore not manipulate the legacy database behavior or structure until all applications are converted. I also needed to replicate some existing behavior, whether I liked it or not!

When I posted this question I was using EF6 for my data access layer. Having changed to EF Core 1.1.0 I can now utilise the 'HasDefaultValueSql' setting in my table mappings which resolves my particular issue by allowing me to set a value whenever the DB is updated.