1

there is a way in mysql / mariadb triggers to know if a value is present (set) in the update query?

For example:

    create table MyTest (
    col1 varchar(45),
    col2 varchar(45)
);

insert into MyTest values ( 'val1','val2');

So when I execute

update MyTest set col1 = 'val1.1'

in the trigger I would like to know that col2 is not set.

Thank You

UPDATE (I changed also table schema for better explain my problem)

I have setup a trigger like this

CREATE DEFINER=`root`@`localhost` TRIGGER `MyTest_BEFORE_UPDATE` BEFORE UPDATE ON `MyTest` FOR EACH ROW
BEGIN
    IF (NEW.col2 <=> OLD.col2) THEN
        SET NEW.col2 = 'triggered';
    END IF;
END

If I call

UPDATE MyTest set col1 = 'val1.3'

table will be (this is ok)

+------+---------+
|col1  | col2    |
+------+---------+
|val1.3|triggered|
+------+---------+

but if i call

UPDATE MyTest SET col1='val1.4',col2='val2.4'

first time table is ok

+------+---------+
|col1  | col2    |
+------+---------+
|val1.4|val2.4   |
+------+---------+

but a second update like this

UPDATE MyTest SET col1='val1.5',col2='2.4'

table will be:

+------+---------+
|col1  | col2    |
+------+---------+
|val1.5|triggered|
+------+---------+

instead i would like it should be

+------+---------+
|col1  | col2    |
+------+---------+
|val1.5|val2.4   |
+------+---------+

There is some way i can check this?

Thanks

1

The Boolean expression

NEW.col2 <=> OLD.col2

can be used in an IF statement (or elsewhere). Notes:

  • It tests for whether col2 is changing, it does not test whether you specified col2 without changing it.
  • NULL is properly handled because of the <=> operator.

UPDATE (based on further details) See if this comes closer:

IF ( NEW.col1 <=> OLD.col1  -- testing only col1 for changes
 AND NEW.col2 IS NULL)      -- avoiding clobbering col2
    NEW.col2 = 'triggered';

(You haven't yet specified enough use cases to provide a complete solution, but maybe I have given you enough building blocks?)

  • I have updated my question with example because this isn't working the way I need. – CeccoMe Aug 18 '16 at 7:55
  • If col2 is declared NULL, what do you want? – Rick James Aug 18 '16 at 14:48
  • (Updated my answer) – Rick James Aug 18 '16 at 14:52
  • Sorry, I already tested vs NULL, but this doesn't work... NEW.col2 is never set to 'triggered' I think that if a column isn't set, in the trigger NEW.col is set equal to OLD.col – CeccoMe Aug 19 '16 at 8:03

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.