Mysql trigger select into variable

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

Kenny Linsky

1,6683 gold badges17 silver badges41 bronze badges

asked Nov 27, 2008 at 20:33

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

answered Nov 29, 2008 at 23:29

Bill KarwinBill Karwin

511k83 gold badges644 silver badges803 bronze badges

8

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

outis

73.3k20 gold badges146 silver badges216 bronze badges

answered May 15, 2009 at 12:43

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

END;`;

answered Nov 27, 2008 at 21:58

IgorSIgorS

1738 bronze badges

1

I'm posting this solution because I had a hard time finding what I needed. This post got me close enough (+1 for that thank you), and here is the final solution for rearranging column data before insert if the data matches a test.

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

1. The Unique Key is a composite of rridprefix + rrid
2. Before I took over there was no constraint preventing duplicate unique keys
3. We needed to combine two tables (one full of duplicates) into the main table which now has the constraint on the composite key (so merging fails because the gaining table won't allow the duplicates from the unclean table)
4. on duplicate key is less than ideal because the columns are too numerous and may change

Anyway, here is the trigger that puts any duplicate keys into a legacy column while allowing us to store the legacy, bad data (and not trigger the gaining tables composite, unique key).

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

answered Dec 20, 2016 at 15:42

WEBjujuWEBjuju

5,2454 gold badges26 silver badges34 bronze badges

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

answered Nov 27, 2008 at 23:29

dkretzdkretz

37.1k13 gold badges79 silver badges136 bronze badges

As far I think I understood your question I believe that u can simply declare your variable inside "DECLARE" and then after the "begin" u can use 'select into " you variable" ' statement. the code would look like this:

DECLARE
YourVar  varchar(50);
begin 
select ID into YourVar  from table
where ...

answered Jul 1, 2020 at 7:34

1

How do I assign a value to a variable in MySQL trigger?

Can we use trigger with SELECT statement?

How do you insert values into a table using triggers?

What is Definer in MySQL trigger?