patternsqlMinor
Checking for fields not on SET clause on before update trigger on MySQL
Viewed 0 times
triggerupdatefieldscheckingmysqlforbeforenotclauseset
Problem
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)
So, what I want to do is when
e.g.
Current Dataset
Operations and Expected Results
Case 1
Case 2
Case 3
Case 4
So, I've tried using this trigger but it doesn't seem to work the way I want it to work.
I think my main confusion here is how NEW values in the columns not updated are represented in triggers. If I update only the
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.
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" 1Operations 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;
ENDI 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.
Solution
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.)Context
StackExchange Database Administrators Q#128465, answer score: 2
Revisions (0)
No revisions yet.