CODEWITHSUNDEEP
mysqltriggers
Dmytro
Dmytro

Reputation: 141

Cannot create trigger

I am just want to create after insert trigger to insert a new row in history table. Why am I getting an error when I run the query?

orders

create table orders
(
    id int auto_increment
        primary key,
    id_user int not null,
    picture_name varchar(100) not null,
    time date not null,
    constraint FK_USER
        foreign key (id_user) references stef.users (id)
)
;

create index FK_USER_idx
    on orders (id_user)
;

history

create table history
(
    id int auto_increment
        primary key,
    id_order int not null,
    id_action int not null,
    time date not null,
    constraint FK_ORDER
        foreign key (id_order) references stef.orders (id),
    constraint FK_ACTION
        foreign key (id_action) references stef.actions (id)
)
;

create index FK_ORDER_idx
    on history (id_order)
;

create index FK_ACTION_idx
    on history (id_action)
;

my trigger...

CREATE TRIGGER orders_AFTER_INSERT
AFTER INSERT ON stef.orders
FOR EACH ROW
  BEGIN
    INSERT INTO history('id_order', 'id_action', 'time')
    VALUES (NEW.id, 1, NOW());
  END;

I am just want to create after insert trigger to insert a new row in history table. Why am I getting an error when I run the query?

Upvotes: 0

Views: 1260

Answers (2)

Uueerdo
Uueerdo

Reputation: 15961

Try this

DELIMITER $$
CREATE TRIGGER orders_AFTER_INSERT
AFTER INSERT ON stef.orders
FOR EACH ROW
BEGIN
    INSERT INTO history(`id_order`, `id_action`, `time`)
    VALUES (NEW.id, 1, NOW());
END$$
DELIMITER ;

You need to temporarily override the delimiter so MySQL can differentiate between the end of a statement within the body of a trigger (or procedure, or function) and the end of the body.

Edit: Single quotes (') are only ever used to denote string values, for field names use the ` (or in some configurations the ")

Upvotes: 2

Dmytro
Dmytro

Reputation: 141

CREATE TRIGGER orders_AFTER_INSERT
AFTER INSERT ON stef.orders
FOR EACH ROW
BEGIN
    INSERT INTO stef.history()
    VALUES (null, NEW.id, 1, NOW());
END

Upvotes: 0

Related Questions