Skip to content

Instantly share code, notes, and snippets.

@imliam imliam/trigger.sql
Created Mar 8, 2017

Embed
What would you like to do?
MySQL - At least one column must have a value
/*
|--------------------------------------------------------------------------
| At least one column must have a value
|--------------------------------------------------------------------------
|
| This is a simple pair of triggers for MySQL databases that will disallow
| a row to be inserted into a table unless at least one of two predefined
| columns has a value set.
|
*/
DELIMITER $$
CREATE TRIGGER InsertParentNotNull BEFORE INSERT ON content
FOR EACH ROW BEGIN
IF (NEW.column_one IS NULL AND NEW.column_two IS NULL) THEN
SIGNAL SQLSTATE "45000"
SET MESSAGE_TEXT = "\"column_one\" and \"column_two\" cannot both be null";
END IF;
END;
CREATE TRIGGER UpdateParentNotNull BEFORE UPDATE ON content
FOR EACH ROW BEGIN
IF (NEW.column_one IS NULL AND NEW.column_two IS NULL) THEN
SIGNAL SQLSTATE "45000"
SET MESSAGE_TEXT = "\"column_one\" and \"column_two\" cannot both be null";
END IF;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.