audit.md

# 交易审计

Orders 表

```sql
create table orders
(
    id          int primary key auto_increment,
    item_id     int,
    amount      int,
    unit_price  decimal(12, 4),
    total       decimal(12, 4),
    description varchar(2000),
    ts          timestamp default now()
);
```

记录了未完成的订单。审计部门现在需要记录其变更——新增或删除,该表不会发生update——即将修改都记录到 orders_log 表

```sql
create table orders_log
(
    log_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()
);
```

那么应该如何做？

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

```sql
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');
```

## 选项

### A

```sql
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');

```

### B

```sql

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');
```


### C

```sql
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');
    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');
```

### D

```sql
create trigger in_orders after insert, after delete 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');
```