MySQL Trigger - Storing a SELECT in a variable

Question

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

User · Answer

CREATE TRIGGER  category before ins tr  BEFORE INSERT ON  category    FOR EACH ROW BEGIN       SET  tableId   SELECT id FROM dummy LIMIT 1      END

User · Answer

CREATE TRIGGER clearcamcdr AFTER INSERT ON  asteriskcdrdb   cdr   FOR EACH ROW BEGIN   SET  INC    SELECT sip inc FROM trunks LIMIT 1     IF NEW billsec  gt 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

User · Answer

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

User · Answer

CREATE TRIGGER  category before ins tr  BEFORE INSERT ON  category    FOR EACH ROW BEGIN       SET  tableId   SELECT id FROM dummy LIMIT 1      END

User · Answer

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    The Unique Key is a composite of rridprefix   rrid Before I took over there was no constraint preventing duplicate unique keys 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  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  gt  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

User · Answer

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

User · Answer

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

User · Answer

As far I think I understood your question I believe that u can simply declare your variable inside  quot DECLARE quot  and then after the  quot begin quot  u can use  select into  quot  you variable quot    statement  the code would look like this  DECLARE YourVar  varchar 50   begin  select ID into YourVar  from table where

User · Answer

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

User · Answer

CREATE TRIGGER  category before ins tr  BEFORE INSERT ON  category    FOR EACH ROW BEGIN       SET  tableId   SELECT id FROM dummy LIMIT 1      END

User · Answer

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    The Unique Key is a composite of rridprefix   rrid Before I took over there was no constraint preventing duplicate unique keys 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  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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

CREATE TRIGGER clearcamcdr AFTER INSERT ON  asteriskcdrdb   cdr   FOR EACH ROW BEGIN   SET  INC    SELECT sip inc FROM trunks LIMIT 1     IF NEW billsec  gt 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

User · Answer

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

User · Answer

CREATE TRIGGER  category before ins tr  BEFORE INSERT ON  category    FOR EACH ROW BEGIN       SET  tableId   SELECT id FROM dummy LIMIT 1      END

User · Answer

As far I think I understood your question I believe that u can simply declare your variable inside  quot DECLARE quot  and then after the  quot begin quot  u can use  select into  quot  you variable quot    statement  the code would look like this  DECLARE YourVar  varchar 50   begin  select ID into YourVar  from table where

[mysql] MySQL Trigger - Storing a SELECT in a variable

Examples related to mysql

Examples related to sql

Examples related to triggers