Как правильно создать триггер?

Question

[Chesterfield25]

У меня есть таблица а точнее запрос который создает таблицу в бд

CREATE TABLE IF NOT EXISTS `airdrops` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) NOT NULL,
    `token_name` varchar(256) NOT NULL,
    `image` varchar(256) NOT NULL,
    `status` int(11) NOT NULL DEFAULT 0,
    `created` datetime NOT NULL,
    `start_date` datetime NOT NULL,
    `end_date` datetime NOT NULL,
    `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

После создания таблицы я добавляю несколько материалов с датами и триггер на изменения статуса в строке статус но он по какой то причине не хочет работать корректно.
Мне нужно сделать так что если дата создания `created` меньше за дату старта `start_date` то статус был 0, если наступила дата старта `start_date` и текущая дата меньше или равна дате окончания `end_date` то статус был 1, а если текущая дата больше за дату окончания `end_date` то статус был 2.

Мой триггер

DELIMITER $$

CREATE TRIGGER update_status_before_insert BEFORE INSERT ON airdrops
FOR EACH ROW
BEGIN
    IF NEW.created < NEW.start_date THEN
        SET NEW.status = 0;
    ELSEIF NOW() > NEW.end_date THEN
        SET NEW.status = 2;
    ELSEIF NEW.created <= NEW.start_date AND NEW.created < NEW.end_date THEN
        SET NEW.status = 1;
    END IF;
END$$

CREATE TRIGGER update_status_before_update BEFORE UPDATE ON airdrops
FOR EACH ROW
BEGIN
    IF NEW.created < NEW.start_date THEN
        SET NEW.status = 0;
    ELSEIF NOW() > NEW.end_date THEN
        SET NEW.status = 2;
    ELSEIF NEW.created <= NEW.start_date AND NEW.created < NEW.end_date THEN
        SET NEW.status = 1;
    END IF;
END$$

DELIMITER ;

Пробовал так но нечего не помогло

DELIMITER $$

CREATE TRIGGER update_status_before_insert BEFORE INSERT ON airdrops
FOR EACH ROW
BEGIN
    IF NEW.created < NEW.start_date THEN
        SET NEW.status = 0;
    ELSEIF NOW() > NEW.end_date THEN
        SET NEW.status = 2;
    ELSEIF NOW() >= NEW.start_date AND NEW.start_date <= NEW.end_date THEN
        SET NEW.status = 1;
    END IF;
END$$

CREATE TRIGGER update_status_before_update BEFORE UPDATE ON airdrops
FOR EACH ROW
BEGIN
    IF NEW.created < NEW.start_date THEN
        SET NEW.status = 0;
    ELSEIF NOW() > NEW.end_date THEN
        SET NEW.status = 2;
    ELSEIF NOW() >= NEW.start_date AND NEW.start_date <= NEW.end_date THEN
        SET NEW.status = 1;
    END IF;
END$$

DELIMITER ;

Comments

[Akina]

Мне нужно сделать так что если дата создания `created` меньше за дату старта `start_date` то статус был 0, если наступила дата старта `start_date` и текущая дата меньше или равна дате окончания `end_date` то статус был 1, а если текущая дата больше за дату окончания `end_date` то статус был 2.

Переведите, пожалуйста, это малопонятное нечто на русский язык.
А заодно поясните, как оно связано с представленными кодами. Особенно интересует наличие в коде текущего штампа времени NOW(), о котором в описании нет ни полслова.

Answer 1

[Константин Цветков]

У вас все поля в пределах одной таблицы. Почему не вычисляемое поле?

Как указал Akina использовать NOW() нельзя, но с учётом того, что срабатывание триггера выполняется только один раз, в момент на которое указывает поле modified можно использовать его. Также приведя условия статуса к верной логике получаем (MySQL online):

-- create
CREATE TABLE airdrops (
    start_date datetime NOT NULL,
    end_date datetime NOT NULL,
    modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status int(11) AS ( case
                          when modified < start_date THEN 0
                          when modified >= start_date AND modified <= end_date THEN 1
                          when modified > end_date THEN 2
                          else -1
                        end )
    );
-- insert
INSERT INTO airdrops (start_date, end_date) VALUES ( '2024-03-12', '2024-03-31') ;
INSERT INTO airdrops (start_date, end_date) VALUES ( '2024-03-01', '2024-03-31') ;
INSERT INTO airdrops (start_date, end_date) VALUES ( '2024-03-02', '2024-03-09') ;
-- fetch 
SELECT * FROM airdrops;

статус 2 устанавливался автоматически если текущая дата

Использовать представление (MySQL online)

-- create
CREATE TABLE airdrops (
    start_date datetime NOT NULL,
    end_date datetime NOT NULL
    );
-- insert
INSERT INTO airdrops (start_date, end_date) VALUES ( '2024-03-12', '2024-03-31') ;
INSERT INTO airdrops (start_date, end_date) VALUES ( '2024-03-01', '2024-03-31') ;
INSERT INTO airdrops (start_date, end_date) VALUES ( '2024-03-02', '2024-03-09') ;
-- view
CREATE VIEW airdrops_status
AS
SELECT start_date, end_date,
      case
        when NOW() < start_date THEN 0
        when NOW() >= start_date AND NOW() <= end_date THEN 1
        when NOW() > end_date THEN 2
        else -1
      end AS status
  FROM airdrops;
-- fetch 
SELECT * FROM airdrops_status;

Comments

[Akina]

В коде (но не в описании) есть NOW()... при его наличии возможность использовать вычисляемое поле отпадает сама собой.

[Константин Цветков]

Akina, Да, я не учел это ограничение MySQL. Значит нужно делать представление, так как триггер с текущем временем перестаёт быть правильным сразу после выполнения.

[Chesterfield25]

Akina, Ну а как быть если мне нужно что бы статус 2 устанавливался автоматически если текущая дата больше за end_date?

[Константин Цветков]

Chesterfield25, Использовать представление.

[Chesterfield25]

Константин Цветков, что такое представление?

[Константин Цветков]

Chesterfield25, См. ответ.

Answer 2

[Akina]

Автор хочет, чтобы значение поля записи зависело как от текущих значений других полей этой записи, так и от текущего штампа времени. То есть, требуется, чтобы у поля было недетерминированное значение.

Задача не решается вычисляемым полем - в его выражении не допускается недетерминированных конструкций.

Для решения задачи потребуется:
1) trigger, который обработает начальные условия
2) event procedure, которая обработает изменение значения по наступлении заданного момента времени

Однако условия на формирование значения поля:

1) написаны так, что хрен поймёшь (даже непонятно, с какого языка это переводили)
2) не описывают ВСЕ возможные вариации факторов (например, непонятно, какое должно быть значение, если ни одно из трёх условий не выполняется)

Ну и, как правильно предлагает Константин Цветков, разумнее не изменять данные в таблице, а рассчитывать актуальный статус в момент, когда он требуется.

Comments

[Константин Цветков]

текущего штампа времени … не допускается

CREATE TABLE [dbo].[TestDatatime](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Begin] [datetime] NOT NULL,
	[DiffNow]  AS (datediff(second,[Begin],getdate())))

[Akina]

Константин Цветков, у MySQL (а именно таким тегом помечен вопрос) глаза выскочат, если он это увидит...

Error number: 3763; Symbol: ER_GENERATED_COLUMN_NAMED_FUNCTION_IS_NOT_ALLOWED; SQLSTATE: HY000
Message: Expression of generated column '%s' contains a disallowed function: %s.