MySQL Trigger - Storing a select in a variable

Question

I have a trigger in which I want to have a variable which holds an INT i get from a (SELECT..). So I can use it in two IF statements; so I don't have to call the (SELECT...) twice. How do you declare/use variables in MySQL triggers?

Answer 1

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

END;`;

Answer 2

Or you can just include the SELECT statement in the SQL that's invoking the trigger, so its passed in as one of the columns in the trigger row(s). As long as you're certain it will infallibly return only one row (hence one value). (And, of course, it must not return a value that interacts with the logic in the trigger, but that's true in any case.)

Answer 3

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 foo
FOR EACH ROW BEGIN
  DECLARE x INT;
  SET x = NEW.i;
  SET @a = x; -- set user variable outside trigger
END//

DELIMITER ;

SET @a = 0;

SELECT @a; -- returns 0

INSERT 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 foo
FOR 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 foo
FOR 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//

Answer 4

CREATE TRIGGER clearcamcdr AFTER INSERT ON asteriskcdrdb.cdr FOR EACH ROW BEGIN 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