3

Let's say I have a table.

CREATE TABLE IF NOT EXISTS Test (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Foo tinyint(1) unsigned NOT NULL DEFAULT 0,
  Bar varchar(255) NOT NULL,
  PRIMARY KEY (Id)
);

And before insert trigger:

DROP TRIGGER IF EXISTS `test_update`;
DELIMITER //
CREATE TRIGGER `test_update` BEFORE UPDATE ON `Test`
FOR EACH ROW BEGIN
  IF NEW.Foo IS NULL THEN
    -- do something
  END IF;
END
//
DELIMITER ;

Via trigger I check if Foo column is actually passed into update statement. Otherwise I do something.

My query:

UPDATE Test
SET Bar = '23'
WHERE Id = 1;

How to properly detect Foo was not set?

userlond
  • 3,632
  • 2
  • 36
  • 53
  • Your trigger is asssigning `Bar` values, not `FOO` values. Are you sure that there is something happening here which should not be? – Tim Biegeleisen Dec 25 '15 at 07:18
  • Thanks for comment. Yes, I'm sure. `Foo` is a flag, which absence in update statement is a condition of handling `Bar` column in a alternative way (in simplified example - just rewrite to 'Hello world') – userlond Dec 25 '15 at 07:20
  • From what I can tell, the new and old values for `Foo` should always be the same. – Tim Biegeleisen Dec 25 '15 at 07:22
  • @TimBiegeleisen, it seems like u r right. Mysql populates `NEW` in before update triggers with **potential result** of update, not with the data **actually passed into**. – userlond Dec 25 '15 at 08:21
  • You might want to update your question and show several `UPDATE` statements and the results you see in your table. – Tim Biegeleisen Dec 25 '15 at 08:23

1 Answers1

0

You have created table with:

Foo tinyint(1) unsigned NOT NULL DEFAULT 0,

thus, you will never get NULL at 'Foo', because of [tinyint][1]: 1 byte, -128 to +127 / 0 to 255 (unsigned)

Just quick test:

mysql> INSERT INTO `Test` (`Id`, `Foo`, `Bar`) VALUES   (1, 'ttt', 'La-la-la');
ERROR 1366 (HY000): Incorrect integer value: 'ttt' for column 'Foo' at row 1
malyy
  • 859
  • 5
  • 10
  • I've changed Foo to tinyint signed default null, result is the same... Actually I don't need Foo to be null. I need get column value, passed into update query. – userlond Dec 25 '15 at 08:13