25/7/23数据库第五次作业

练习：

要求
1.定义触发器实现在产品表(product)中每多一个产品,就在操作表(operate)中记录操作方式和时间以及编号记录。
注:操作说明:标记执行delete、insert、update2.定义触发器实现在产品表(product)中每更新一个产品，就在操作表(operate)中记录操作方式和时间以及编号记录。

3.定义触发器实现在产品表(product)中每制除一个产品就，在操作表(operate)中记录操作方式和时间以及编号记录.

product表内容：

operate表内容：

创建触发器，每次激活触发器后，都会更新operate表

• 创建product_after_insert_trigger

• mysql> create trigger product_after_insert_trigger after insert on product for each row insert into operate values (null,'insert',now());

• 

• 创建product_after_update_trigger

•  create trigger product_after_update_trigger after update on product for each row insert into operate values (null,'update',now());

• 

• 创建product_after_delete_trigger

•  create trigger  product_after_delete_trigger  after update on product for each row insert into operate values (null,'delete',now());

• 

• 执行语句向operate表插入操作方法和操作时间

测试：

插入触发器测试

mysql> insert into product(name,func,com,address) value('海尔洗衣机','洗衣机','海尔','合肥');

mysql> insert into product(name,func,com,address) value('ps5','游戏','sony','京东');

更新触发器测试

mysql> update product set address='上海' where name='ps5';

删除触发器测试

mysql> delete from product where name='ps5';

删除所建的触发器

mysql> drop trigger if exists porduct_after_insert_trigger;

mysql> drop trigger if exists porduct_after_update_trigger;

mysql> drop trigger if exists porduct_after_delete_trigger;

1.创建customers表

mysql> create table customers(
    -> customer_id int auto_increment primary key,
    -> name varchar(100),
    -> email varchar(100));

创建customers——archives表

mysql> create table customer_archives(
    -> customer_id int,
    -> name varchar(100),
    -> email varchar(100),
    -> archived_at timestamp);

2.创建触发器

mysql> delimiter //
mysql> create trigger arichive_customer_before_delete
    -> before delete on customers
    -> for each row
    -> begin

                -- 将删除的客户记录插入到 archive 表中
    -> insert into customer_archives (customer_id,name,email,archived_at)
    -> values (old.customer_id,old.name,old.email,now());
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter  ;