trigger.sql

create table orders_log
(
    event_id          int primary key auto_increment,
    id int,
    item_id     int,
    amount      int,
    unit_price  decimal(12, 4),
    total       decimal(12, 4),
    description varchar(2000),
    ts          timestamp,
    direction   varchar(16),
    log_at      timestamp default now()
);

create trigger in_orders after insert on orders
    for each row insert into orders_log(id, item_id, amount, unit_price, total, description, ts, direction)
    values(NEW.id, NEW.item_id, NEW.amount, NEW.unit_price, NEW.total, NEW.description, NEW.ts, 'in');

create trigger out_orders after delete on orders
    for each row insert into orders_log(id, item_id, amount, unit_price, total, description, ts, direction)
                 values(OLD.id, OLD.item_id, OLD.amount, OLD.unit_price, OLD.total, OLD.description, OLD.ts, 'out');