Reputation: 893
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
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