CODEWITHSUNDEEP
mysqlsqltriggers
ray6080
ray6080

Reputation: 893

MySQL Trigger auto increment

I'd like to create a mysql(v5.5) trigger which performs auto increment by a random number each time I insert a value. Here is what I am doing:

DELIMITER $$

DROP TRIGGER IF EXISTS actCodeAuto
$$
CREATE TRIGGER actCodeAuto BEFORE INSERT ON activity
FOR EACH ROW BEGIN
  DECLARE newNumber INT DEFAULT 1;
  SELECT Max(activityCode) INTO newNumber FROM activity;
  IF(newNumber=NULL) THEN
    SET new.activityCode=100;
  ELSE
    SET new.activityCode = newNumber+FLOOR(1+RAND()*3);
  END IF;
END$$

DELIMITER ;

I want to auto increment a random number to the max value of column activityCode each time I insert a row, except when I insert a row for the first time, I want to set Max(activityCode) to 100.

While I insert a row for the first time, MySQL just reports an error: (1048, "Column 'activityCode' cannot be null")

Any ideas? Thanks!

Upvotes: 2

Views: 1594

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 563021

Null is not equal to null in SQL.

So this will never be true:

IF(newNumber=NULL) THEN

You need:

IF(newNumber IS NULL) THEN

Upvotes: 2

Related Questions