MySQL Trigger - saving SELECT in a variable

Question

I have a trigger in which I want to have a variable that contains the INT that I get from SELECT , so I can use it in two IF statements instead of calling SELECT twice. How do you declare / use variables in MySQL triggers?

Answer 1

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

Here is 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 one value, not a set of rows or a set of columns. For example, if your query returns a single value in practice, this is normal, 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 one 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 2

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

Answer 3

 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$$ 

Do not try this @home

Answer 4

Or you can simply include the SELECT statement in SQL that calls the trigger, so it passed as one of the columns in the run line (s). While you are sure that it will accurately return only one row (hence one value). (And, of course, it should not return a value that interacts with the logic in the trigger, but this is true anyway.)

Answer 5

I am posting this solution because it was hard for me to find what I needed. This post got me close enough (+1 thanks for that), and here is the final solution for reordering the column data before inserting if the data matches the test .

Note: this is from a legacy project inherited from where:

• The unique key is the rridprefix + rrid
• Before I took part, there were no restrictions preventing duplication of unique keys
• We needed to combine two tables (one of the duplicates) into the main table, which now has a restriction on the composite key (therefore, the merge is not performed because the win table will not allow duplicate data from an unclean table)
• on duplicate key less than ideal because the columns are too numerous and can change

In any case, here is a trigger that puts any duplicate keys in the old column, allowing us to store obsolete, bad data (rather than run compound tablets with accumulation, a unique key) .. p>

 BEGIN -- prevent duplicate composite keys when merging in archive to main SET @EXIST_COMPOSITE_KEY = (SELECT count(*) FROM patientrecords where rridprefix = NEW.rridprefix and rrid = NEW.rrid); -- if the composite key to be introduced during merge exists, rearrange the data for insert IF @EXIST_COMPOSITE_KEY > 0 THEN -- set the incoming column data this way (if composite key exists) -- the legacy duplicate rrid field will help us keep the bad data SET NEW.legacyduperrid = NEW.rrid; -- allow the following block to set the new rrid appropriately SET NEW.rrid = null; END IF; -- legacy code tried set the rrid (race condition), now the db does it SET NEW.rrid = ( SELECT if(NEW.rrid is null and NEW.legacyduperrid is null, IFNULL(MAX(rrid), 0) + 1, NEW.rrid) FROM patientrecords WHERE rridprefix = NEW.rridprefix ); END