Geek asks and answers

How to determine if a field / column affects an UPDATE statement

We welcome everyone and thank you for your answers and comments.

I have a table with several fields, among which version, last_modifiedandmodified_by

I am writing a trigger:

  • increase versionby 1 after each / any update,
  • set last_modifiedto the current timestamp,
  • set the ID of the user who made the most recent changes to modified_by, and
  • prevent the programmer from ignoring / forgetting to set modified_by = useridthe UPDATE in the instruction by raising the signal (in this case).

How can i achieve this?

I tried to check if isnull(NEW.modified_by), but then I realized that it NEW.modified_bytakes the same meaning as OLD.modified_byif it was not affected. In addition, checking that NEW.modified_byis equal OLD.modified_bydoes not quite do this, as it may be a custom modifier of a record that was previously modified by himself.

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

+3
source share
3 answers

, , , . , , , , . , .

, , , , . , , user_id ( , ). user_id "" , . , user_id , . , .

+3

. tinyint isDirty, 1, , 0, "".

, NEW OLD 0 1 . "", , 100, "0", "1". , : 0 1, 100 ( ) "", , .

, , , 0, , , . :

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
$$
+3

As far as I know, the only option is to check each value of the NEW column compared to OLD.

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

More articles:


All Articles