CODEWITHSUNDEEP
mysqlsqltriggersdatabase-trigger
Mohamad Najib
Mohamad Najib

Reputation: 49

How to create Complex IF Condition in MySQL Trigger

I have situation with my trigger here. My IF statement is not working properly. I am stuck and have spent many hours trying to troubleshoot it.

This is my code :

CREATE DEFINER=`administrator`@`%` TRIGGER `My_Trigger` AFTER INSERT ON `my_first_table` FOR EACH ROW BEGIN
DECLARE A FLOAT default 0;
SET A = NEW.Data;

IF (A < 60.00 && NEW.Node <> 'XXX') OR (A < 60.00 && NEW.Node <> 'YYY') OR (A < 60.00 && NEW.Node <> 'ZZZ')
  THEN
    INSERT INTO `my_database`.`my_table` VALUES ('Data 1', 'Data 2', 'Data 3', 'Data 4', NEW.Node, A);
  END IF;
END

So, If I only using one Node (not using OR in if statement), the trigger working perfectly.

The question is, How to make IF statement with a lot of OR?

Thanks

Upvotes: 0

Views: 290

Answers (3)

stojimm
stojimm

Reputation: 36

Have you tried using || instead of OR or try encapsulating the three conditions. Try if the example below works for you.

IF (A < 60.00 AND (NEW.Node <> 'XXX' OR NEW.Node <> 'YYY' OR NEW.Node <> 'ZZZ'))
  THEN
    INSERT INTO `my_database`.`my_table` VALUES ('Data 1', 'Data 2', 'Data 3', 'Data 4', NEW.Node, A);
  END IF;
END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You seem to want NOT IN:

IF (A < 60.00 AND NEW.Node NOT IN ('XXX', 'YYY', 'ZZZ')) THEN
    INSERT INTO `my_database`.`my_table`
        VALUES ('Data 1', 'Data 2', 'Data 3', 'Data 4', NEW.Node, A);
END IF;

I also strongly encourage you to use AND and OR -- which are the standard boolean operators in SQL -- and not || and && which are MySQL extensions.

Upvotes: 0

Nick
Nick

Reputation: 147166

If you only want to Insert when A is below 60, and the node is not XXX, YYY, and ZZZ, you need to use AND logic instead. Try this:

IF (A < 60.00 AND NEW.Node <> 'XXX' AND NEW.Node <> 'YYY' AND NEW.Node <> 'ZZZ')

Upvotes: 1

Related Questions