MySQL: Trigger

MySQL Programming Reference Snippet

Contents

  1. Snippets
    1. Prevent operation
    2. Insert NULL instead of empty string
  2. Links

Snippets

Prevent operation

To prevent INSERT/UPDATE/DELETE operation on some condition, create a BEFORE-trigger and send signal 45000 when operation shouldn't be allowed.

  DELIMITER $$
  CREATE TRIGGER TriggerName BEFORE INSERT ON TableName
  FOR EACH ROW
  BEGIN
    IF NEW.FieldName = 'InvalidValue' THEN
      SIGNAL SQLSTATE '45000';
    END IF;
  END$$
  DELIMITER ;

Insert NULL instead of empty string


  DELIMITER $$
  CREATE TRIGGER TriggerName BEFORE INSERT ON TableName
  FOR EACH ROW
  BEGIN
    IF TRIM(NEW.FieldName) = '' THEN
      SET NEW.FieldName = NULL;
    END IF;
  END$$
  DELIMITER ;

Links