How to determine if a field/column is affected by an UPDATE statement

Hello everyone and thank you for your answers and comments.

I have a table with several fields, among which are version, last_modified and modified_by

I'm writting a trigger to:

• increase version by 1 after every/any update,
• set last_modified to the current timestamp,
• set the id of the user who made the latest changes into modified_by, and
• prevent the programmer from ignoring/forgetting to set modified_by = userid in the UPDATE statement by raising a signal (in such case).

How can I achieve this?

I tried checking if isnull(NEW.modified_by), but then realized that NEW.modified_by takes the same value as OLD.modified_by if it wasn't affected. Also, checking if NEW.modified_by equals OLD.modified_by doesn开发者_开发知识库't quite make it, since it could be a user modifiyng a record previously modified by himself.

Is there a way to determine which fields where affected by the UPDATE statement? Or if a particular field (modified_by) was affected?

I cannot find anything that will allow you to inspect the incoming information to see which fields are being affected. While I know you are trying to stop this issue at the trigger level, it might be prudent to require all table writes to go through a stored procedure. That way you could require the user field.

If that isn't a possibility, I think you might need to get tricky. For example, you could require that the user_id be written to two fields (create an extra column that is blank for this purpose). Then, compare the user_id in the dummy column to the one you are updating. Once you figure out if you need to modify the user_id or not, blank out the dummy column again. It isn't pretty, but it would get the job done.

I had a similar issue. I have a table with a tinyint column named isDirty, and wanted to set it to 1 when the row is updated, and clear it to 0 when the row has been "cleaned".

The problem is, the combination of NEW and OLD with values of 0 and 1 didn't give me enough values to solve my problem. So instead, I made a "rule" that when the column was updated to a value of 100, it was set to the clean value of "0", anything else set it to "1". The reason this works is that this column will only ever have one of 2 values, 0 or 1, so you can use 100 (or any value of your choice) as the "flag" that indicates that it is clean.

I know that it sounds a little backwards to set it to a non-zero value to get back to 0, but that's the direction I chose and it works. Here's what that trigger looks like:

CREATE TRIGGER calls_update BEFORE UPDATE ON `calls`
    FOR EACH ROW
    BEGIN
        IF ( NEW.isDirty = 100 ) THEN
            SET NEW.isDirty = 0;
    ELSE
            SET NEW.isDirty = 1;
        END IF;
    END
$$

As far as I know, your only option is to check each column's NEW value against the OLD.

SET `ColumnAChanged` = NEW.ColumnA <=> OLD.ColumnA;
SET `ColumnBChanged` = NEW.ColumnB <=> OLD.ColumnB;