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:
new.payDate := curdate();
->"." is not valid at this position, expecting an identifierEND;
-> is not valid at this position, expecting an EOF ....I checked a bunch of sites and questions, including this
please help me understand what is wrong
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 |