mysqltriggers

MYSQL Trigger on insert setting a date condition to now if other inserted column value is true


I am trying to learn more about the triggers. While setting up a table orders

CREATE TABLE orders
 (
 id INT unsigned not null auto_increment,
 tag INT unsigned not null,
 entered datetime default current_timestamp,
 completed boolean not null default false,
 payed boolean not null default false,
 payDate date,
 pickedUp boolean not null default false,
 pickUpDate date,
 workType enum('job0', 'job1', 'Custom') not null,
primary key (id)
 )

My goal was to set the payDate value to curdate() on the entry if the order is payed on placement and leave it empty (default) if payment has not been made

I have tried multiple phrasings for triggers but this is the final result:

CREATE TRIGGER payDate_onEnter 
 BEFORE INSERT ON orders
 FOR EACH ROW
  BEGIN
    IF new.payed is TRUE then
        new.payDate := curdate();
    END IF;
 END;

MySql workbench tells me that:

I checked a bunch of sites and questions, including this

please help me understand what is wrong


Solution

  • Trigger not needed.

    CREATE TABLE orders
     (
     id INT unsigned not null auto_increment,
     tag INT unsigned not null,
     entered datetime default current_timestamp,
     completed boolean not null default false,
     payed boolean not null default false,
     payDate date DEFAULT (CASE WHEN payed THEN CURRENT_DATE END),
     pickedUp boolean not null default false,
     pickUpDate date,
     workType enum('job0', 'job1', 'Custom') not null,
    primary key (id)
     )
    
    INSERT INTO orders (tag, payed, workType) VALUES
    (1, FALSE, 1),
    (2, TRUE, 2);
    SELECT * FROM orders;
    
    id tag entered completed payed payDate pickedUp pickUpDate workType
    1 1 2024-11-01 16:41:10 0 0 null 0 null job0
    2 2 2024-11-01 16:41:10 0 1 2024-11-01 0 null job1

    fiddle