MySQL Select statement return null when inside a Trigger

I'm creating a trigger in MySQL but have a slight issue.

Here's the trigger code :

DELIMITER $$
DROP TRIGGER IF EXISTS cfmaj$$
CREATE TRIGGER cfmaj AFTER INSERT ON vtiger_cf_608
FOR EACH ROW
BEGIN
DECLARE sortid,pickvalue INT;
SET @pickvalue = (SELECT id FROM vtiger_picklistvalues_seq),
@sort_id = (SELECT max(sortid) FROM vtiger_role2picklist WHERE roleid = 'H5' AND picklistid = 47); 
UPDATE vtiger_cf_608_seq SET id = id+1;
UPDATE开发者_如何转开发 vtiger_picklistvalues_seq SET id = id+1;
INSERT INTO vtiger_role2picklist (roleid,picklistvalueid,picklistid,sortid) VALUES ('H5',@pickvalue,47,@sort_id);
END;
$$
DELIMITER ;

Everything works fine except that @sort_id equal null. Funny thing is when querying with just

(SELECT max(sortid) FROM vtiger_role2picklist WHERE roleid = 'H5' AND picklistid = 47)

,the query works perfectly.

I'm a bit confused.

Thanks in advance.

Should you not be using @var := value? := makes it into an assignment, otherwise MySQL interprets it as an equality test.

You're using

SET @pickvalue = (SELECT id FROM vtiger_picklistvalues_seq),
@sort_id = (SELECT max(sortid) FROM vtiger_role2picklist WHERE roleid = 'H5' AND picklistid = 47); 

instead of

SET pickvalue = (SELECT id FROM vtiger_picklistvalues_seq),
sort_id = (SELECT max(sortid) FROM vtiger_role2picklist WHERE roleid = 'H5' AND picklistid = 47); 

Take into account that set = @sort_id is totally different from set = sort_id

Then on your

INSERT INTO vtiger_role2picklist (roleid,picklistvalueid,picklistid,sortid) VALUES ('H5',@pickvalue,47,@sort_id);

You're using @sortid instead of your sort_id variable, which has not the value previously set.

I'm not an expert in sql, but maybe it's because you don't use SELECT max(sortid) AS xxx.

If it doesn't work, try to do a SELECT ... INTO:

DECLARE var_sort_id INT;

SELECT MAX(sortid) AS maxsortid INTO var_sort_id FROM vtiger_role2picklist WHERE roleid = 'H5' AND picklistid = 47;

Hope it works.

EDIT : Why dont't you separate each query, like:

SET pickvalue = (SELECT id FROM vtiger_picklistvalues_seq);
SET sort_id = (SELECT max(sortid) FROM vtiger_role2picklist WHERE roleid = 'H5' AND picklistid = 47);