MySQL触发器 - 将SELECT存储在变量中

我有一个触发器,我希望有一个变量,它保存一个INT SELECT,我可以在两个IF语句中使用它,而不是调用SELECT两次.如何在MySQL触发器中声明/使用变量？

您可以使用DECLARE语法在MySQL触发器中声明局部变量.

这是一个例子:

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

为变量赋值时,必须确保查询仅返回单个值,而不是一组行或一组列.例如,如果您的查询在实践中返回单个值,则可以,但只要它返回多行,就会得到" ERROR 1242: Subquery returns more than 1 row".

您可以使用LIMIT或MAX()确保将局部变量设置为单个值.

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

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

END;`;

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

别试试@ home