MySQL-trigger – Een SELECT opslaan in een variabele

Ik heb een trigger waarin ik een variabele wil hebben die een INT bevat die ik krijg van een SELECT, zodat ik deze in twee IF-statements kan gebruiken in plaats van de SELECTtweemaal. Hoe declareer/gebruik je variabelen in MySQL-triggers?

Antwoord 1, autoriteit 100%

Je kunt lokale variabelen declareren in MySQL-triggers, met de syntaxis DECLARE.

Hier is een voorbeeld:

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

Als u een waarde aan een variabele toewijst, moet u ervoor zorgen dat de query slechts één waarde retourneert, niet een reeks rijen of een reeks kolommen. Als uw zoekopdracht in de praktijk bijvoorbeeld een enkele waarde retourneert, is dat goed, maar zodra deze meer dan één rij retourneert, krijgt u “ERROR 1242: Subquery returns more than 1 row“.

U kunt LIMITof MAX()gebruiken om ervoor te zorgen dat de lokale variabele op één waarde is ingesteld.

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

Antwoord 2, autoriteit 12%

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

Antwoord 3, autoriteit 12%

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

Probeer dit niet @home

Antwoord 4, autoriteit 7%

Ik plaats deze oplossing omdat ik het moeilijk vond om te vinden wat ik nodig had. Dit bericht bracht me dichtbij genoeg (+1 daarvoor bedankt), en hier is de definitieve oplossing voor het herschikken van kolomgegevens voordat ze worden ingevoegd als de gegevens overeenkomen met een test.

Opmerking: dit is van een verouderd projectdat ik heb geërfd waar:

1. De unieke sleutel is een samenstelling van rridprefix+ rrid
2. Voordat ik het overnam, was er geen beperking om dubbele unieke sleutels te voorkomen
3. We moesten twee tabellen (een vol met duplicaten) combineren in de hoofdtabel die nu de beperking heeft op de samengestelde sleutel (dus het samenvoegen mislukt omdat de winnende tabel de duplicaten van de onreine tabel niet toestaat)
4. on duplicate keyis niet ideaal omdat de kolommen te talrijk zijn en kunnen veranderen

Hoe dan ook, hier is de trigger die dubbele sleutels in een oude kolom plaatst, terwijl we de oude, slechte gegevens kunnen opslaan (en niet de samengestelde, unieke sleutel van de winnende tabellen activeren).

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

Antwoord 5, autoriteit 4%

Of je kunt gewoon de SELECT-instructie opnemen in de SQL die de trigger aanroept, zodat het wordt doorgegeven als een van de kolommen in de triggerrij(en). Zolang je zeker weet dat het onfeilbaar slechts één rij retourneert (vandaar één waarde). (En het mag natuurlijk geen waarde retourneren die interageert met de logica in de trigger, maar dat is in ieder geval waar.)

Antwoord 6, autoriteit 2%

Voor zover ik denk dat ik je vraag heb begrepen
Ik geloof dat u uw variabele eenvoudig kunt declareren in “DECLARE”
en dan na het “begin” kunt u de instructie ‘selecteer in “u variabele” ‘ gebruiken.
de code zou er als volgt uitzien:

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