4

I want to set a before update trigger that sets a column to a given value if an update query is done and that column has no new value on the set clause.

The table I'm using. (Sort of)

DROP TABLE IF EXISTS 'hex';
CREATE TABLE 'hex' (
    'id'    INT(11) NOT NULL AUTO_INCREMENT,
    'chunk' VARCHAR(45),
    'flag'  TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id')
);

So, what I want to do is when hex.chunk gets a new value and flag doesn't, flag is set to 0.

e.g.

Current Dataset

id   chunk           flag
1    "Antelope"      0
2    "Barracuda"     1
3    "Capybara"      0
4    "Dolphin"       1

Operations and Expected Results

Case 1 UPDATE hex SET chunk = "Antelope" WHERE id = 1; doesn't update the flag since chunk doesn't get a new value.

Case 2 UPDATE hex SET chunk = "Elephant" WHERE id = 2; updates the flag to 0 since chunk changes its value and no data for flag is passed.

Case 3 UPDATE hex SET chunk = "Capybara", flag = '1' WHERE id = 3; chunk and flag updates as queried.

Case 4 UPDATE hex SET chunk = "Ferrovax", flag = '0' WHERE id = 3 Same as case 3.

So, I've tried using this trigger but it doesn't seem to work the way I want it to work.

CREATE TRIGGER 'flag_the_hex'
BEFORE UPDATE ON 'hex'
FOR EACH ROW
BEGIN
    IF (NEW.flag IS NULL AND NEW.chunk != OLD.chunk)
    THEN
        SET NEW.flag = 0;
    END IF;
END

I think my main confusion here is how NEW values in the columns not updated are represented in triggers. If I update only the chunk column, how is the NEW value for flag represented? Null? Or does it just not exist yet?

Please keep in mind that there may be more columns in the table I'm using.

Thanks for reading.

EDIT: I asked this question over at StackOverflow and thought that may not be the appropriate place to ask this question. So, I asked this here and deleted it over there.

2

flag will be what it had been before the UPDATE started. Maybe it would feel 'right' to use OLD.flag instead of NEW.flag, at least in the if?

Addenda

NEW.flag contains the new value. NEW.flag <=> OLD.flag checks to see if it changed. I doubt if there is any way to see if it was set but not changed. (Note that I used the "NULL-safe equal" operator.)

  • Sorry, but nope.. Still doesn't work the way I want it to. I don't really care about the old value of the flag. I just want it to set to 0 if no value is passed. Is there anyway to know which values are set in the query? – TheLoneWoof Feb 7 '16 at 0:18
  • Oh. I see. So, the cases I stated are not possible to recreate using only triggers. You may want to edit your answer to include your previous comment so I can select it as the answer. Thanks, by the way. – TheLoneWoof Feb 7 '16 at 0:56
  • Comment folded in; deleting comment. – Rick James Feb 7 '16 at 1:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.