实验4：Mysql—触发器

淩亂°似流年 2023-07-02 08:25 12阅读 0赞

#### 1.实验目的 ####

掌握数据库触发器的设计和使用方法

#### 2.实验内容和要求 ####

定义BEFORE触发器和AFTER触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

#### 3.实验重点和难点 ####

实验重点：触发器的定义。  
实验难点：利用触发器实现较为复杂的用户自定义完整性。

#### 4.实验步骤 ####

此次实验还是延用之前实验创建的数据库表：boats,sailors.reservers

> AFTER触发器

(1) 在sailors表上定义一个UPDATE触发器，当sailors表中元组的属性sid被更新时，意味着所有表中的sid都要相应更新，因此需要用触发器维护reserves里元组的sid属性。

create trigger sid_update after update
    on sailors for each row 
    begin
    update reserves set sid=NEW.sid where sid=OLD.sid;
    end

验证定义在sailors表上的AFTER UPDATE触发器.  
一开始，sid为22的在sailors中有一条记录，在reserves中有四条记录:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70]  
将sailors中的sid为22的值更新为23之后，reserves中的sid也一起更新:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 1]  
从上图可以看到UPDATE触发器触发成功  
(2) 在reserves表上定义一个INSERT触发器，当对课程reserves表执行任何插入操作后触发器被启动，以确保插入元组的bid属性即船只的序号是合法的，受boats表的bid约束,检查插入时的参照完整性

create trigger insert_trigger after insert
    on reserves for each row
    begin
    if(NEW.bid not in(select bid from boats))
    then 
    delete from reserves where bid=NEW.bid;
    end if;
    end

验证定义在reserves表上的AFTER INSERT触发器.  
首先查看boats表和reserves表中的记录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 2]  
在reserves表中插入一条bid为99的记录(boats中不含有)发现无法成功，但是插入bid=102(boats中含有的bid)发现成功了  
![在这里插入图片描述][20200128131139695.png]  
从上图可以看到INSERT触发器触发成功  
(3) 在boats表上定义一个DELETE触发器，当删除的元组的bid不在删除后的boats表中时，且reserves中存在包含这个bid值的元组时，触发器被启动，以确保boats中发生删除时，reserves的参照完整性。

create trigger timeslot_check2 after delete
    on boats for each row 
    begin
    if(OLD.bid not in (select bid from boats) and 
    OLD.bid in (select bid from reserves))
    then
    insert into boats values(OLD);
    end if;
    end

验证定义在boats表上的AFTER DELETE触发器.  
首先查看boats表和reserves表中的记录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 3]  
在boats表中删除一条bid为102的记录(reserves中含有)发现无法成功，但是删除bid=108(ereserves中不含有的bid)发现成功了  
![在这里插入图片描述][20200128131151857.png]  
从上图可以看到DELETE触发器触发成功

> BEFORE触发器  
> Before触发器的update，insert，delete语句语法和after触发器基本类似，故此下面不再赘述全部三种语句，只拿BEFORE INSERT触发器做为例子。

sailors表上定义一个before insert触发器，插入一条记录时，假设所插入的sname的值为空白则表明该e水手的姓名d发生缺失，所以定义此触发器在分数值条件满足时触发用null值代替空白

create trigger setnull before insert 
    on sailors for each row
    begin
    if(NEW.sname = ' ')
    then 
    set NEW.sname = null;
    end if;
    end

验证定义在sailors表上的BEFORE INSERT触发器  
假设新增一名水手，水手的sid=200,rate=8,age=50,sname暂时不知道:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 4]  
从上图可以看出sailors表上的BEFORE INSERT触发器触发成功。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200128131106298.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200128131112780.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200128131130755.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4,size_16,color_FFFFFF,t_70
[20200128131139695.png]: https://img-blog.csdnimg.cn/20200128131139695.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200128131147575.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4,size_16,color_FFFFFF,t_70
[20200128131151857.png]: https://img-blog.csdnimg.cn/20200128131151857.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200128131204363.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQwODc1MDc4,size_16,color_FFFFFF,t_70