StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, I would recommend changing your table structure from something like this:</p> <pre><code>id field1 field2 field3 field4 timestamp </code></pre> <p>into something like this:</p> <pre><code>Table 1 ------- id fieldKey value timestamp 1 1 42 12:03am 2 3 'Cow' 1:45am 3 2 'Moo' 2:33am 4 4 99 3:59am Table 2 ------- fieldKey fieldLabel 1 Field One 2 Field Two 3 Event One 4 Event Two </code></pre> <p>Second, you can achieve what you want by copying the old value or calculating the hash of the field values (for long fields) and storing them in another field (oldField1, oldField2, etc.) along with the time stamp, then poll the table. I believe all databases have a hash function, like SHA1('xyz') or MD5('abc'), though it might have a slightly different name in each one. </p> <p>Then when you poll the table for changes, you see the row that has changed, and you can do a field by field comparison to find out which field has changed. So you would compare field1 to oldField1, field2 to oldField2, field3 to oldField3, etc. For blobs, you could compare SHA1(field4) to oldField4). </p> <p>Create a stored procedure trigger to copy the fields when you update the time stamp. </p> <p>Another solution is to create a data change log table which only stores changes. It would be inserted into via a trigger stored procedure, and it would look like this</p> <pre><code>id tableName rowKey fieldName oldValue newValue timestamp 1 WINDOWS_EVENTS 42 event1 stable crash 4:44am </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload