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