CODEWITHSUNDEEP
mysqlsqltriggersmariadb
user2914191
user2914191

Reputation: 897

MySQL Trigger Check if Row Field Exists

I created a trigger on a table as follows:

delimiter //
CREATE TRIGGER tb_ins BEFORE UPDATE ON tb
FOR EACH ROW
BEGIN
    IF (NEW.size <> size) THEN

    END IF;
END;//

The problem is, individually updating any field other than size gives me an error that no such field is found.

For instance:

UPDATE tb SET color = 'red' WHERE id = 1;

Gives me an error that no field is found because the update does not contain a size field which the trigger requires.

I need to know if there's a way to check if a specific ROW field exists in my trigger function. How can this be accomplished?

I need something equivalent to:

IF EXISTS(ROW.size) THEN

END IF;

Upvotes: 1

Views: 1768

Answers (1)

spencer7593
spencer7593

Reputation: 108380

The error isn't related to the columns referenced in the UPDATE statement.

The big problem is the unqualified reference to size in the trigger body. If the intent is to detect a change in the value assigned to size, we should be comparing the value of NEW.size to OLD.size.

DELIMITER $$

DROP TRIGGER IF EXISTS tb_ins $$

CREATE TRIGGER tb_ins 
BEFORE UPDATE ON tb
FOR EACH ROW
BEGIN
   IF NOT (NEW.size <=> OLD.size) THEN
      -- value of size column has been modified
      BEGIN END;
   END IF;
END$$

It's not valid to have nothing between THEN and END IF. We have to have something there. MySQL tolerates an empty BEGIN END; block, so we can use that as a no-op.

It's not clear what you are attempting to achieve.

Upvotes: 1

Related Questions