MySQL запускает обновление поля до значения id

Я хотел бы иметь триггер для выполнения следующей операции для вставленных записей:

 # pseudocode
 if new.group_id is null
    set new.group_id = new.id
 else
    # don't touch it
 end

Более ясно: скажем, у меня одна таблица с тремя столбцами: id первичный ключ, group_id int, value varchar.

Когда я вставляю с group_id следующим образом:

INSERT INTO table(value, group_id) VALUES ('a', 10)

Я бы хотел:

id | group_id | value
---+----------+------
 1 |       10 | a

но когда я опускаю group_id:

INSERT INTO table(value) VALUES ('b')

он должен быть автоматически установлен в id этой записи:

id | group_id | value
---+----------+------
 2 |        2 | b

Возможно ли это с помощью триггера? (Я знаю, что я могу обновить запись после вставки, но с триггером было бы лучше.)

Ответы

Ответ 1

Я не знаю, как это сделать в одном утверждении, даже с помощью триггера.

Решение триггера, предложенное @Lucky, будет выглядеть в MySQL:

CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
  SET NEW.group_id = COALESCE(NEW.group_id, NEW.id);
END

Однако есть проблема. В фазе BEFORE INSERT автоматически сгенерированное значение id еще не создано. Поэтому, если group_id имеет значение null, по умолчанию он равен NEW.id, который всегда равен 0.

Но если вы измените этот триггер на огонь во время фазы AFTER INSERT, поэтому у вас есть доступ к сгенерированному значению NEW.id, вы не можете изменять значения столбца.

MySQL не поддерживает выражения для DEFAULT столбца, поэтому вы также не можете объявить это поведение в определении таблицы.

Единственное решение - сделать INSERT, а затем сразу сделать UPDATE, чтобы изменить group_id, если он не установлен.

INSERT INTO MyTable (group_id, value) VALUES (NULL, 'a');
UPDATE MyTable SET group_id = COALESCE(group_id, id) WHERE id = LAST_INSERT_ID();

Ответ 2

Я отвечаю здесь, как в принятом ответе Билл Карвин утверждает:

В фазе ПЕРЕД ВСТАВКОЙ значение автогенерации id не было созданный еще. Поэтому, если group_id равно null, по умолчанию используется значение NEW.id, которое всегда 0.

У меня есть для этого ответ - для OP (и для посетителей), вот несколько моментов: Вы не можете обновить таблицу, откуда запускается триггер, для этого вы получите Ошибка 1442:

Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

1. Чтобы обновить новую строку Используйте триггер BEFORE INSERT ON, таким образом вы можете обновить все поля для новой строки, которые могут быть доступны с помощью оператора NEW, т.е.

set NEW.group_id = NEW.id

2. Установите значение auto_increment перед вставкой:

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='MyTable'

Подводя итог - триггер SQL для 'd будет выглядеть следующим образом:

DELIMITER //
DROP TRIGGER IF EXISTS MyTrigger//
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
    IF new.group_id IS NULL
        set @auto_id := (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
                         WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA=DATABASE() ); 
        set NEW.group_id = @auto_id;
    ENF IF;
END;
//
DELIMITER ;

Ответ 3

Я считаю, что это сработает для вас

У меня есть две таблицы

test_b: a_id, value
test_c: a_id, value

И вот триггер на вставке теста b. Он проверяет, является ли a_id нулевым, и если он вставляет 0

CREATE TRIGGER test_b AFTER INSERT ON test_b
  FOR EACH ROW 
    INSERT INTO test_c (a_id, value) VALUES (IFNULL(NEW.a_id, 0),NEW.value)

Ответ 4

Это работает для меня

DELIMITER $$
CREATE TRIGGER 'myTriggerNameHere'
BEFORE INSERT ON 'table' FOR EACH ROW
BEGIN
    SET NEW.group_id = IF(NEW.group_id IS NULL, LAST_INSERT_ID()+1, NEW.group_id);
END;
$$
DELIMITER ;

Ответ 5

Этот триггер должен делать то, что вы просили.

   CREATE TRIGGER mytrigger BEFORE INSERT ON mytable
          IF new.group_id IS NULL
            SET new.group_id = new.id
          END IF
      END;

Он копируется из очень похожего примера на странице документации по MYSQL.

Ответ 6

В этой ситуации триггер кажется излишним. Просто примените значение по умолчанию.

CREATE TABLE `test` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `value` varchar(100) NOT NULL,
    `group_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2'
)