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.
id chunk flag 1 "Antelope" 0 2 "Barracuda" 1 3 "Capybara" 0 4 "Dolphin" 1
Operations and Expected Results
UPDATE hex SET chunk = "Antelope" WHERE id = 1; doesn't update the
chunk doesn't get a new value.
UPDATE hex SET chunk = "Elephant" WHERE id = 2; updates the
chunk changes its value and no data for flag is passed.
UPDATE hex SET chunk = "Capybara", flag = '1' WHERE id = 3;
flag updates as queried.
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.