CODEWITHSUNDEEP
mysqlsql
Dani-Br
Dani-Br

Reputation: 2457

automatic convert 0 or empty string to NULL

[in MySQL]
Is there any way to constraint any field to not be 0, and if I got 0 value convert it to NULL.
For example, two products can't have the same serial number, but product can be without serial number at all.
I'm using right now this:(for few fields) 

CREATE TABLE products (
    sn DECIMAL(8,2),
    UNIQE (sn),
    ...........
) ENGINE=InnoDB;
CREATE TRIGGER prod_zero1 BEFORE UPDATE ON products FOR EACH ROW
    SET NEW.sn=NULLIF(NEW.sn,0);

Is there any shorter\faster way how to do it ?
Other problem is that when I try to use this for few fields in the same table, I got massege: 

CREATE TRIGGER prod_zero2 BEFORE UPDATE ON products FOR EACH ROW
    SET NEW.name=NULLIF(NEW.name,"");
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Thanks

Upvotes: 3

Views: 3637

Answers (2)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26749

Do not use multiple triggers, use one trigger that takes care for all of the field 

CREATE TRIGGER prod_zero2 BEFORE UPDATE ON products FOR EACH ROW
BEGIN
SET NEW.sn=NULLIF(NEW.sn,0);
SET NEW.name=NULLIF(NEW.name,"");
END;

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65587

You can do this in a single trigger.

Here are the two columns from your example combined into one trigger. I just did an update triggers because that's what you had in your example, but you'll probably want another trigger to do the same thing for inserts, too.

DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_upd_products $$

CREATE TRIGGER tr_b_upd_products BEFORE UPDATE ON products FOR EACH ROW BEGIN
  IF (NEW.sn = 0)
  THEN
    SET NEW.sn=NULL;
  END IF;

  IF (NEW.name = '')
  THEN
    SET NEW.name=NULL;
  END IF;
END $$

DELIMITER ;

Upvotes: 0

Related Questions