您如何看待这种在mysql中记录更改并具有某种审计跟踪的方法

Question

I've been reading through several topics now and did some research about logging changes to a mysql table. First let me explain my situation:

I've a ticket system with a table: 'ticket'

As of now I've created triggers which will enter a duplicate entry in my table: 'ticket_history' which has "action" "user" and "timestamp" as additional columns. After some weeks and testing I'm somewhat not happy with that build since every change is creating a full copy of my row in the history table. I do understand that disk space is cheap and I should not worry about it but in order to retrieve some kind of log or nice looking history for the user is painful, at least for me. Also with the trigger I've written I get a new row in the history even if there is no change. But this is just a design flaw of my trigger!

Here my trigger:

BEFORE UPDATE ON ticket FOR EACH ROW
BEGIN
INSERT INTO ticket_history
SET
    idticket = NEW.idticket,
    time_arrival = NEW.time_arrival,
    idticket_status = NEW.idticket_status,
    tmp_user = NEW.tmp_user,
    action = 'update',
    timestamp = NOW();
END

---

My new approach in order to avoid having triggers

After spening some time on this topic I came up with an approach I would like to discuss and implement. But first I would have some questions about that:

My idea is to create a new table:

    id   sql_fwd        sql_bwd      keys      values    user       timestamp
    -------------------------------------------------------------------------
    1    UPDATE...      UPDATE...    status    5         14          12345678
    2    UPDATE...      UPDATE...    status    4         7           12345678

The flow would look like this in my mind:

At first I would select something or more from the DB:

SELECT keys FROM ticket;

Then I display the data in 2 input fields:

<input name="key" value="value" /> <input type="hidden" name="key" value="value" />

Hit submit and give it to my function:

I would start with a SELECT again: SELECT * FROM ticket; and make sure that the hidden input field == the value from the latest select. If so I can proceed and know that no other user has changed something in the meanwhile. If the hidden field does not match I bring the user back to the form and display a message.

Next I would build the SQL Queries for the action and also the query to undo those changes.

$sql_fwd = "UPDATE ticket 
            SET idticket_status = 1
            WHERE idticket = '".$c_get['id']."';";

$sql_bwd = "UPDATE ticket 
            SET idticket_status = 0
            WHERE idticket = '".$c_get['id']."';";

Having that I run the UPDATE on ticket and insert a new entry in my new table for logging.

With that I can try to catch possible overwrites while two users are editing the same ticket in the same time and for my history I could simply look up the keys and values and generate some kind of list. Also having the SQL_BWD I simply can undo changes.

My questions to that would be:

• Would it be noticeable doing an additional select everytime I want to update something?
• Do I lose some benefits I would have with triggers?
• Are there any big disadvantages
• Are there any functions on my mysql server or with php which already do something like that?
• Or is there might be a much easier way to do something like that
• Is maybe a slight change to my trigger I've now already enough?
• If I understad this right MySQL is only performing an update if the value has changed but the trigger is executed anyways right?
• If I'm able to change the trigger, can I still prevent somehow the overwriting of data while 2 users try to edit the ticket the same time on the mysql server or would I do this anyways with PHP?

Thank you for the help already

Answer 1

I've answered a similar question before. You'll see some good alternatives in that question.

In your case, I think you're merging several concerns - one is "storing an audit trail", and the other is "managing the case where many clients may want to update a single row".

Firstly, I don't like triggers. They are a side effect of some other action, and for non-trivial cases, they make debugging much harder. A poorly designed trigger or audit table can really slow down your application, and you have to make sure that your trigger logic is coordinated between lots of developers. I realize this is personal preference and bias.

Secondly, in my experience, the requirement is rarely "show the status of this one table over time" - it's nearly always "allow me to see what happened to the system over time", and if that requirement exists at all, it's usually fairly high priority. With a ticketing system, for instance, you probably want the name and email address of the users who created, and changed the ticket status; the name of the category/classification, perhaps the name of the project etc. All of those attributes are likely to be foreign keys on to other tables. And when something does happen that requires audit, the requirement is likely "let me see immediately", not "get a database developer to spend hours trying to piece together the picture from 8 different history tables. In a ticketing system, it's likely a requirement for the ticket detail screen to show this.

If all that is true, then I don't think history tables populated by triggers are a good idea - you have to build all the business logic into two sets of code, one to show the "regular" application, and one to show the "audit trail".

Instead, you might want to build "time" into your data model (that was the point of my answer to the other question).

Since then, a new style of data architecture has come along, known as CQRS. This requires a very different way of looking at application design, but it is explicitly designed for reactive applications; these offer much nicer ways of dealing with the "what happens if someone edits the record while the current user is completing the form" question. Stack Overflow is an example - we can see, whilst typing our comments or answers, whether the question was updated, or other answers or comments are posted. There's a reactive library for PHP.