Question 227930 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql BEFORE UPDATE TRIGGER set certain column value before the actual update on the same column

I want to set STATUS column value to null before an update happen in TABLE_A regardless whether the update changes the STATUS column value or not. Suppose that the update statement looks like this
UPDATE TABLE_A SET STATUS=0, STATUS_A=1;,
i want to change the STATUS column value to null first before it is set to 0. Or if the update statement looks like this UPDATE TABLE_A SET STATUS_A=1;, i want to change the STATUS column value to null first. I created a trigger before update code like below

delimiter $$
 CREATE TRIGGER DECISION_CHANGES BEFORE UPDATE ON TABLE_A
     FOR EACH ROW
     BEGIN
     IF NEW.STATUS_A<>OLD.STATUS_A THEN
     SET `STATUS` = NULL;
     ELSEIF NEW.STATUS_T1<>OLD.STATUS_T1 AND NEW.STATUS_TL1<>OLD.STATUS_TL1 THEN
     SET `STATUS` = NULL;
     ELSEIF NEW.STATUS_T2<>OLD.STATUS_T2 AND NEW.STATUS_TL2<>OLD.STATUS_TL2 THEN
     SET `STATUS` = NULL;
     END IF;
END$$
delimiter ;

But those code give me error probably because i can only set new.STATUS not STATUS. But if i did that, it will be like overwriting the actual update values won’t it?(In the end it would write null, not 0)

How do i do that with trigger?if i can’t do that with trigger, is there any workaround?
Thank you.

UPDATE 1.
I dont really understand how exactly the data flow works on trigger with before update clause but i tried to code it like below. Will it does things as i expect it will?

    delimiter $$
    CREATE TRIGGER DECISION_CHANGES BEFORE UPDATE ON TABLE_A
    FOR EACH ROW
    BEGIN
    DECLARE tempSTATUS INT;
            DECLARE FLAG INT;
    IF OLD.`STATUS`<>NEW.`STATUS` THEN
       SET tempSTATUS=NEW.`STATUS`;
       SET FLAG=1;
    ELSEIF NEW.STATUS_T1<>OLD.STATUS_T1 AND NEW.STATUS_TL1<>OLD.STATUS_TL1 THEN
       SET tempSTATUS=NEW.`STATUS`;
       SET FLAG=1;
    ELSEIF NEW.STATUS_T2<>OLD.STATUS_T2 AND NEW.STATUS_TL2<>OLD.STATUS_TL2 THEN
       SET tempSTATUS=NEW.`STATUS`;
       SET FLAG=1;
       END IF;
    IF NEW.STATUS_A<>OLD.STATUS_A THEN
       SET `STATUS` = NULL;
    ELSEIF NEW.STATUS_T1<>OLD.STATUS_T1 AND NEW.STATUS_TL1<>OLD.STATUS_TL1 THEN
       SET `STATUS` = NULL;
    ELSEIF NEW.STATUS_T2<>OLD.STATUS_T2 AND NEW.STATUS_TL2<>OLD.STATUS_TL2 THEN
       SET `STATUS` = NULL;
    END IF;
    IF FLAG=1 THEN
       SET NEW.`STATUS`=tempSTATUS;
    END IF;
    END$$
        delimiter ;
Tags:

2

Answers


  1. 0

    But those code give me error probably because i can only set
    new.STATUS not STATUS. But if i did that, it will be like overwriting
    the actual update values won’t it?(In the end it would write null, not
    0)

    Yes, but you can work around that. If the value of STATUS is changed in the update, the result you want in that field is the new value. So it does not matter it it was set to NULL in-between or not.

    You may perform your trigger to set its value to NULL only when it not changed by the update, like this.

    DELIMITER $$
CREATE TRIGGER `DECISION_CHANGES` BEFORE UPDATE ON `TABLE_A` FOR EACH ROW
IF NEW.`STATUS` = OLD.`STATUS` THEN SET NEW.`STATUS` = NULL;
END IF
$$
DELIMITER ;

    Example with initial values like this :

    STATUS STATUS_A
    1 2

    The following query :

    UPDATE `TABLE_A`
SET `STATUS` = '3', `STATUS_A` = '4'
WHERE `STATUS` = '1' AND `STATUS_A` = '2'

    Results in :

    STATUS STATUS_A
    3 4

    While the following query :

    UPDATE `TABLE_A`
SET `STATUS_A` = '4'
WHERE `STATUS` = '1' AND `STATUS_A` = '2'

    Results in :

    STATUS STATUS_A
    NULL 4
    Login or Signup to reply.
  2. 0

    Suppose that the update statement looks like this UPDATE TABLE_A SET STATUS=0, STATUS_A=1;, i want to change the STATUS column value to null first before it is set to 0.

    Or if the update statement looks like this UPDATE TABLE_A SET STATUS_A=1;, i want to change the STATUS column value to null first.

    I dont really understand how exactly the data flow works on trigger with before update clause

    The term/word "first" is not correct.

    When you execute UPDATE then, for each row which must be updated:

    • The server creates temporary row which is a copy of current row state
    • SET clause expressions of UPDATE are applied, some columns values in temporary row are changed, and new row state is formed
    • BEFORE trigger additionally edits the values in this row state, for the columns which were both set or skipped in SET clause
    • The row with the data state which is obtained after SET and trigger expressions is saved instead of old data into the table

    I.e. you must simply set NEW.column to needed value. NULL in your case.

    PS. In multiple-table UPDATE the server creates temporary row for each current row, in each table. If the row of some table must be updated a lot of time (together with different rows from other tables) then only one indefinite (by fact – the most first) row state is saved, and all another states are ignored. See fiddle.

    PPS. If more than one BEFORE trigger exists then they are applied one-by-one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search