第七次作业-CSDN博客

本文链接：https://blog.csdn.net/NewYui/article/details/140472695

mysql> delimiter !!
mysql> create trigger product_bf_insert
    -> before insert on  product
    -> for each row
    -> begin
    -> insert into operate (Op_type,Op_time)
    -> values ('insert','insert');
    -> end !!

Query OK, 0 rows affected (0.01 sec)

mysql> \d !!
mysql> create trigger product_af_update
    -> after update on product
    -> for each row
    -> begin
    -> insert into operate(Op_type,Op_time)
    -> values ('update','update');
    -> end !!
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger product_af_del
    -> after delete on product
    -> for each row
    -> begin
    -> insert into operate (Op_type,Op_time)
    -> values ('insert','insert');
    -> end !!
Query OK, 0 rows affected (0.01 sec)

mysql> \d ;
mysql> show triggers;
+-------------------+--------+---------+-----------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger           | Event  | Table   | Statement                                                                   | Timing | Created                | sql_mode
                               | Definer        | character_set_client | collation_connection | Database Collation |
+-------------------+--------+---------+-----------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| product_bf_insert | INSERT | product | begin
insert into operate (Op_type,Op_time)
values ('insert','insert');
end | BEFORE | 2024-07-16 10:38:20.90 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | gbk                  | gbk_chinese_ci       | utf8mb4_0900_ai_ci |
| product_af_update | UPDATE | product | begin
insert into operate(Op_type,Op_time)
values ('update','update');
end  | AFTER  | 2024-07-16 10:40:10.23 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | gbk                  | gbk_chinese_ci       | utf8mb4_0900_ai_ci |
| product_af_del    | DELETE | product | begin
insert into operate (Op_type,Op_time)
values ('insert','insert');
end | AFTER  | 2024-07-16 10:41:55.20 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | gbk                  | gbk_chinese_ci       | utf8mb4_0900_ai_ci |
+-------------------+--------+---------+-----------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO product (Id, Name, `Function`, Company, Address)
    -> VALUES (1, '智能手环', '健康监测、运动追踪', '未来科技公司', '北京海淀区');
Query OK, 1 row affected (0.01 sec)

mysql> desc product;
+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key  | Default | Extra |
| ----- | ---- | ---- | ---- | ------- | ----- |
|       |      |      |      |         |       |

+----------+-------------+------+-----+---------+-------+

| id       | int         | NO   | PRI  | NULL |      |
| -------- | ----------- | ---- | ---- | ---- | ---- |
|          |             |      |      |      |      |
| Name     | varchar(20) | NO   |      | NULL |      |
| ----     | ----------- | ---- | ---- | ---- | ---- |
|          |             |      |      |      |      |
| Function | varchar(50) | YES  |      | NULL |      |
| -------- | ----------- | ---- | ---- | ---- | ---- |
|          |             |      |      |      |      |
| Company  | varchar(20) | NO   |      | NULL |      |
| -------  | ----------- | ---- | ---- | ---- | ---- |
|          |             |      |      |      |      |
| Address  | varchar(20) | YES  |      | NULL |      |
| -------  | ----------- | ---- | ---- | ---- | ---- |
|          |             |      |      |      |      |

+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> update product
    -> SET Name = '智能健康手环', Function = '健康监测、运动追踪、睡眠分析'
    -> WHERE Id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Function = '健康监测、运动追踪、睡眠分析'
WHERE Id = 1' at line 2
mysql> UPDATE product
    -> SET Name = '智能健康手环', `Function` = '健康监测、运动追踪、睡眠分析'
    -> WHERE Id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DELETE FROM product
    -> WHERE Id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from operate;
+-------+---------+---------+

| Op_id | OP_type | Op_time |
| ----- | ------- | ------- |
|       |         |         |

+-------+---------+---------+

| 1    | insert | insert |
| ---- | ------ | ------ |
|      |        |        |
| 2    | update | update |
| ---- | ------ | ------ |
|      |        |        |
| 3    | insert | insert |
| ---- | ------ | ------ |
|      |        |        |

+-------+---------+---------+
3 rows in set (0.00 sec)

mysql> drop trigger product_bf_insert;
Query OK, 0 rows affected (0.01 sec)

mysql> drop trigger product_af_update;
Query OK, 0 rows affected (0.01 sec)

mysql> show triggers;
+----------------+--------+---------+-----------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger        | Event  | Table   | Statement                                                                   | Timing | Created                | sql_mode
                            | Definer        | character_set_client | collation_connection | Database Collation |
+----------------+--------+---------+-----------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| product_af_del | DELETE | product | begin
insert into operate (Op_type,Op_time)
values ('insert','insert');
end | AFTER  | 2024-07-16 10:41:55.20 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | gbk                  | gbk_chinese_ci       | utf8mb4_0900_ai_ci |
+----------------+--------+---------+-----------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)