У меня есть триггер, в котором я хочу иметь переменную, содержащую INT, который я получаю от SELECT
, поэтому я могу использовать его в двух операторах IF вместо того, чтобы дважды вызывать SELECT
. Как вы объявляете/используете переменные в триггерах MySQL?
MySQL Trigger — сохранение SELECT в переменной
Ответы (6)
Вы можете объявлять локальные переменные в триггерах MySQL с синтаксисом DECLARE
.
Вот пример:
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//
@
), чтобы переменная имела значение после выполнения вставки. Затем вы можете проверить, какое значение находится в вашей переменной сеанса.
- person Bill Karwin; 14.04.2019
SET NEW.DoctorName = (select current_user())
, но он показывает мне значение в DoctorName пользователю, который создает триггер.
- person Akash; 14.04.2019
USER()
, а не CURRENT_USER()
. Прочтите dev.mysql.com/doc/refman /8.0/en/: Триггеры и события не имеют опции для определения характеристики SQL SECURITY, поэтому для этих объектов CURRENT_USER() возвращает учетную запись пользователя, определившего объект. Чтобы вернуть вызывающего, используйте USER() или SESSION_USER().
- person Bill Karwin; 14.04.2019
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$$
Не пытайтесь это @дома
Я публикую это решение, потому что мне было трудно найти то, что мне нужно. Этот пост подвел меня достаточно близко (+1 за это, спасибо), и вот окончательное решение для перестановки данных столбца перед вставкой, если данные соответствуют тесту.
Примечание. Это из устаревшего проекта, который я унаследовал, где:
- Уникальный ключ состоит из
rridprefix
+rrid
. - До того, как я вступил во владение, не было никаких ограничений, предотвращающих дублирование уникальных ключей.
- Нам нужно было объединить две таблицы (одна полная дубликатов) в основную таблицу, которая теперь имеет ограничение на составной ключ (поэтому слияние не удается, потому что полученная таблица не допускает дубликатов из нечистой таблицы).
on duplicate 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
Или вы можете просто включить оператор SELECT в SQL, который вызывает триггер, чтобы он передавался как один из столбцов в строке (строках) триггера. Пока вы уверены, что он безошибочно вернет только одну строку (следовательно, одно значение). (И, конечно же, он не должен возвращать значение, взаимодействующее с логикой триггера, но в любом случае это так.)
Насколько я думаю, я понял ваш вопрос, я считаю, что вы можете просто объявить свою переменную внутри DECLARE, а затем, после начала, вы можете использовать оператор «выбрать в вашу переменную». код будет выглядеть так:
DECLARE
YourVar varchar(50);
begin
select ID into YourVar from table
where ...