MySQL Select statement return null when inside a Trigger

Question

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.

Marc B · Accepted Answer · 2011-07-04 14:54:07Z

1

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

answered Jul 4, 2011 at 14:54

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

james_bond · Accepted Answer · 2011-07-04 15:16:55Z

0

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.

edited Jul 4, 2011 at 15:16

answered Jul 4, 2011 at 14:50

james_bond

6,9263 gold badges30 silver badges39 bronze badges

1 Comment

Pluss Over a year ago

Thanks a lot. Actually using @ is correct. The issue here was in my Declare statement. The variable was misspelled. And there I roamed for more than an hour searching for a solution. But you helped me take a closer look at my variable which pointed me toward the solution. Thanks

Anthony Simmon · Accepted Answer · 2011-07-04 14:58:45Z

0

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);

edited Jul 4, 2011 at 14:58

answered Jul 4, 2011 at 14:38

Anthony Simmon

1,61713 silver badges26 bronze badges

1 Comment

Pluss Over a year ago

Seperating the query doesn't change anything. Already tried it.

Collectives™ on Stack Overflow

MySQL Select statement return null when inside a Trigger

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related