CODEWITHSUNDEEP
mysqlstored-procedures
planetp
planetp

Reputation: 16113

MySQL: How to create a set from values inside a trigger?

Suppose I have a MySQL trigger like this:

DELIMITER ;;

CREATE TRIGGER test_trigger AFTER UPDATE ON test_table
FOR EACH ROW BEGIN
    CALL storedRoutine(OLD.col1);
    CALL StoredRoutine(NEW.col1);   
    CALL storedRoutine(OLD.col2);
    CALL StoredRoutine(NEW.col2);
    ...
END;;

How can I create a set of unique values (OLD.col1, NEW.col1, OLD.col2, NEW.col2, etc.) and call the stored routine only for the unique values? For example, if OLD.col1 == NEW.col1, then storedRoutine should be called only once.

Upvotes: 0

Views: 47

Answers (1)

spencer7593
spencer7593

Reputation: 108480

MySQL doesn't support a "set" (or "collection" or "array" type procedure variable.

There's no way to create a "set" apart from running a query that returns a resultset, which would mean defining a cursor, opening the cursor, looping through the rows returned. I wouldn't go that route, that's level of complexity and overhead isn't needed for four values.

With only four values, I'd just code the logic to do the comparisons, since it's only six comparison tests need to be performed. (0+1+2+3)

 IF NOT ( NEW.col1 = OLD.col1 ) THEN

 END IF;

 IF NOT ( OLD.col2 = NEW.col1 OR
          OLD.col2 = OLD.col1 ) THEN

 END IF;

 IF NOT ( NEW.col2 = OLD.col2 OR
          NEW.col2 = NEW.col1 OR
          NEW.col2 = OLD.col1 ) THEN

 END IF;

Note this would still call the procedure multiple times with a NULL value for the argument, I believe the NULL-safe comparator (<=>) works in a stored programs like it does in SQL, but you may not want to call the procedure at all of the argument is NULL...

 IF NOT ( NEW.col2 IS NULL    OR
          NEW.col2 = OLD.col2 OR
          NEW.col2 = NEW.col1 OR
          NEW.col2 = OLD.col1 ) THEN

 END IF;

Upvotes: 1

Related Questions