MySQL Trigger - Storing a SELECT in a variable

You can declare local variables in MySQL triggers, with the DECLARE syntax.

Here's an example:

DROP TABLE IF EXISTS foo;CREATE TABLE FOO (  i SERIAL PRIMARY KEY);DELIMITER //DROP TRIGGER IF EXISTS bar //CREATE TRIGGER bar AFTER INSERT ON fooFOR EACH ROW BEGIN  DECLARE x INT;  SET x = NEW.i;  SET @a = x; -- set user variable outside triggerEND//DELIMITER ;SET @a = 0;SELECT @a; -- returns 0INSERT INTO foo () VALUES ();SELECT @a; -- returns 1, the value it got during the trigger

When you assign a value to a variable, you must ensure that the query returns only a single value, not a set of rows or a set of columns. For instance, if your query returns a single value in practice, it's okay but as soon as it returns more than one row, you get "ERROR 1242: Subquery returns more than 1 row".

You can use LIMIT or MAX() to make sure that the local variable is set to a single value.

CREATE TRIGGER bar AFTER INSERT ON fooFOR EACH ROW BEGIN  DECLARE x INT;  SET x = (SELECT age FROM users WHERE name = 'Bill');   -- ERROR 1242 if more than one row with 'Bill'END//CREATE TRIGGER bar AFTER INSERT ON fooFOR EACH ROW BEGIN  DECLARE x INT;  SET x = (SELECT MAX(age) FROM users WHERE name = 'Bill');  -- OK even when more than one row with 'Bill'END//

`CREATE TRIGGER `category_before_ins_tr` BEFORE INSERT ON `category`  FOR EACH ROWBEGIN    **SET @tableId= (SELECT id FROM dummy LIMIT 1);**END;`;

CREATE TRIGGER clearcamcdr AFTER INSERT ON `asteriskcdrdb`.`cdr` FOR EACH ROWBEGIN  SET @INC = (SELECT sip_inc FROM trunks LIMIT 1);  IF NEW.billsec >1 AND NEW.channel LIKE @INC     AND NEW.dstchannel NOT LIKE ""   THEN    insert into `asteriskcdrdb`.`filtre` (id_appel,date_appel,source,destinataire,duree,sens,commentaire,suivi)       values (NEW.id,NEW.calldate,NEW.src,NEW.dstchannel,NEW.billsec,"entrant","","");   END IF;END$$

Dont try this @ home