CODEWITHSUNDEEP
mysqlregextriggers
Feri
Feri

Reputation: 250

How to trigger a RegEx condition on either of two columns in MySQL?

I have two columns (attributes) in a MySQL table, which a unique RegEx condition is to be applied on either of them via trigger. It means either column1 OR column2's condition suffices for the trigger to return 0.

In other words, how can I put OR between these columns?

I came up with this solution, but I'm not sure there is a better solution:

CREATE DEFINER = CURRENT_USER TRIGGER `MyDatabase`.`TableName_BEFORE_INSERT`
BEFORE INSERT ON `TableName` FOR EACH ROW
BEGIN
IF (NEW.column1,column2 REGEXP '^[augc]+' ) = 0 THEN 
ROLLBACK;
SIGNAL SQLSTATE '12345'
SET MESSAGE_TEXT = 'Your Input can contain only the following letters 'AUGC'. Please correct your input format;
END IF;
END
DELIMITER;

Upvotes: 0

Views: 899

Answers (1)

krokodilko
krokodilko

Reputation: 36127

There are a few problems with your solution, it doesn't even compile, because:

  • COMMIT and ROLLBACK are not allowed in triggers
  • This syntax is wrong: NEW.column1,column2 REGEXP ...
  • there is a syntax error at this place ...letters 'AUGC'. Please..., you need to use double aphostrophes ...letters ''AUGC''. Please...

Try this trigger instead:

CREATE TRIGGER `TableName_BEFORE_INSERT`
BEFORE INSERT ON `TableName` FOR EACH ROW
BEGIN
IF NEW.column1 REGEXP '^[augc]+' OR NEW.column2 REGEXP '^[augc]+'  THEN 
-- ROLLBACK;
SIGNAL SQLSTATE '12345'
SET MESSAGE_TEXT = 'Your Input can contain only the following letters ''AUGC''. Please correct your input format';
END IF;
END
/

Demo: http://sqlfiddle.com/#!9/27f5a

Upvotes: 1

Related Questions