Ensuring Sequential Fields are Filled In MySQL

I'm seeking to configure a table in MySQL that will allow me to enforce an order of field entry.

e.g. Presume a table which includes the following fields

Stage1Timestamp, Stage1AuthorizedBy, Stage2Timestamp, Stage2AuthorizedBy, Stage3Timestamp Stage3AuthorizedBy

开发者_StackOverflow

Where the Timestamps are datetimes to be automatically filled when the AuthorizedBy information is added. (foreignkeys or codes or whatever. That detail is irrelevant.)

What I'm thinking is along these lines:

On definition of AuthorizedBy, set the associated datetime to Now if immediately preceding stage's AuthorizedBy and Timestamp are filled. Otherwise, blank the current value. If either Timestamp or AuthorizedBy value is being blanked out, delete the peer and immediate superior.

This should work fine if MySQL will chain triggers. Does that work? Other thoughts?

---

If you use InnoDB, foreign key constraints are built to check stuff like this for you. Split it up to 3 tables, add keys from stage 2 to stage 1 and from stage 3 to stage 2, and use ON DELETE SET NULL to achive desired behaviour.

Foreign Key Doc

---

I think that you should solve this on the application layer, however here is a DB solution, no need for triggers - just foreign keys. All CreatedAt fields are not null, default to now() -- in MySQL it is called TIMESTAMP.

This is OK only for processes which will not change over time.