sql sever 触发器总结及实验

待我称王封你为后i 2023-01-03 06:15 228阅读 0赞

### sql sever 触发器知识总结及实验 ###

*  一、基础知识
 *  二、sql语句总结
 *  三、实验题目及解答

# 一、基础知识 #

Ⅰ 定义  
触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。

1.  触发器保存在数据库服务器中
2.  任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
3.  触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

Ⅱ 分类  
按照触发事件的语言分类，可以将触发器分成DML触发器和DDL触发器。

1.  DML触发器是指可以被对数据表的insert、update和delete操作所触发的触发器。
2.  DDL触发器是指可以被对数据库对象的create、alter和drop操作所触发的触发器。

按照触发器的触发和执行事件的不同，可以分为after类型触发器和instead of类型触发器。

1.  after类型触发器又被称为后触发触发器，把触发触发器执行的insert、update或者delet语句所代表的事件称为“触发事件”。after类型触发器即是指“触发事件”成功执行完毕后，“被触发事件”执行的触发器。
2.  instead of类型触发器又被称为替换触发器。简单的说，instead of类型的触发器当“触发事件”发生时，系统并不执行“触发事件”的具体操作（比如insert、update或者delete数据），而是直接执行“被触发事件”。

# 二、sql语句总结 #

Ⅰ 创建

create trigger <触发器名>
    on <表名>
    [with encryption] --对创建触发器的文本加密
    after|instead of <update|insert|delete>
    as
    <T-sql语句>

注意：

1.  after类型的触发器只能创建在数据表上，而不能创建在视图上。
2.  instead of类型的触发器可以定义在表上和视图上。对于每个触发操作（insert、update或delete）只能定义一个instead of类型触发器。
3.  一张数据表可以创建多个因同一触发操作而生成的触发器。
4.  **inserted表和deleted表**  
    inserted表和deleted表是系统为每个触发器准备的临时表，存放于内存中。  
    当触发器被触发执行时，inserted表和deleted表将记录触发器执行过程中涉及到的每条记录信息，以方便用户使用和查询。inserted表和deleted表中的记录只能查看，不能修改，当触发器执行完毕后，与之相关的临时表也随之删除。inserted表用来存放当触发操作为insert命令时所产生的数据信息。deleted表用来存放当触发操作为delete命令时所产生的数据信息。update作为修改操作，即相当于删除数据再添加数据，因此，当触发操作为update命令时，既生成deleted表又生成inserted表。

Ⅱ 查看与重命名

exec sp_helptrigger ‘<表名>’[,’<类型>’]
    exec sp_rename <oldname>,<newname>
    exec sp_help ‘<触发器名>’ --查看所有者、创建日期等

Ⅲ 禁用和启用

alter table <表名>
    enable|disable trigger <触发器名>

Ⅳ 删除

drop trigger <触发器名> on <表名>

# 三、实验题目及解答 #

**dz表**  
![基本表一][20210107201921852.png]  
**ts表**  
![基本表二][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDE5Nzc0OQ_size_16_color_FFFFFF_t_70]  
**jy表**  
![基本表三][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDE5Nzc0OQ_size_16_color_FFFFFF_t_70 1]

1、为dz表和ts表设计插入触发器dz\_insert和ts\_insert，分别在插入记录后提示“借书证号为XXXX的读者记录已被插入”或“总编号为XXXX的图书记录已被插入”，其中XXXX为插入的具体字段值；写一条insert语句，插入记录，对插入触发器进行测试。

--------创建dz的插入触发器----------
    create trigger dz_insert
    on dz
    for insert
    as
    declare @id_no nvarchar(10)
    select @id_no=借书证号 from inserted
    print '借书证号为'+@id_no+'的读者记录已被插入'
    
    --------dz插入触发器的测试----------
    insert into dz
    values('999','信息系','ceshi','男',1999-07-01,'教授','1号楼424')
    
    --------创建ts的插入触发器----------
    create trigger ts_insert
    on ts
    for insert
    as
    declare @book_no nvarchar(10)
    select @book_no=总编号 from inserted
    print '总编号为'+@book_no+'的图书记录已被插入'
    
    --------ts插入触发器的测试----------
    insert 
    into ts
    values('999999','ceshi','张三','电子工业出版社','TP',24)

2、为图书表设置更新触发器，根据图书编号来更新书名、作者、出版社、分类号和单价(根据图书编号找到相应记录，然后更新书名、作者等信息)，更新完毕后提示“更新后的总编号为：XX，书名为：XX，作者为：XX，出版社为：XX，分类号为：XX，单价为：XX”(其中XX代表相应字段的值)；输入update语句，例如将图书编号为J007的图书名改为网络安全基础，测试更新触发器。

--------创建ts更新触发器----------
    create trigger ts_update1
    on ts
    for update
    as
    declare @no nvarchar(10)
    declare @book nvarchar(10)
    declare @author nvarchar(4)
    declare @publisher nvarchar(10)
    declare @class nvarchar(2)
    declare @price real
    select @no=总编号,@book=书名,@author=作者,@publisher=出版单位,@class=分类号,@price=单价 from inserted
    print '更新后的总编号为：'+@no+'，书名为：'+@book+'，作者为：'+@author+'，出版社为：'+@publisher+'，分类号为：'+@class+'，单价为：'+cast(@price as varchar)
    
    --------测试ts更新触发器----------
    update ts
    set 书名='大学英语（一）'
    where 总编号='113388'

3、为图书表设置更新触发器，根据总编号来更新书名、作者、出版社、分类号和单价(根据总编号找到相应记录，然后更新书名、作者等信息)，更新完毕后提示“图书表更新成功”；编写测试语句进行测试。

--------创建ts更新触发器2----------
    create trigger ts_update2
    on ts
    for update
    as
    print('图书表更新成功!')
    
    --------测试ts更新触发器----------
    update ts
    set 书名='大学英语（二）'
    where 总编号='113388'

4、为dz表和ts设置删除触发器，若删除dz记录或ts记录，则jy表中的相关读者记录或书籍的记录也应删除，并提示“dz（或ts）表和jy表相关记录已一并删除” ；编写测试语句进行测试。

--------创建ts删除触发器----------
    create trigger dz_delete
    on dz
    for delete
    as
    delete
    from jy
    where 借书证号 in (select 借书证号 from deleted)
    print 'dz表和jy表相关记录已一并删除'
    
    --------测试ts删除触发器----------
    delete
    from dz
    where 借书证号='112'
    
    --------创建dz删除触发器----------
    create trigger ts_delete
    on ts
    for delete
    as
    delete
    from jy
    where 总编号 in (select 总编号 from deleted)
    print 'ts表和jy表相关记录已一并删除'
    
    --------测试dz删除触发器----------
    delete
    from ts
    where 总编号='332211'

5、为ts创建instead of 触发器，当删除某条记录时，显示“对不起，不允许删除操作” ；编写测试语句进行测试。

--------创建ts禁止删除触发器----------
    create trigger ts_sorry
    on ts
    instead of delete
    as
    print '对不起，不允许删除操作'
    
    --------测试ts删除触发器----------
    delete
    from ts
    where 总编号='112266'

6、使用查看命令查看dz表中已创建的触发器；使用命令查看某各触发器代码。

exec sp_helptrigger 'dz'
    
    exec sp_helptext 'dz_insert'
    exec sp_helptext 'dz_delete'

7、修改某触发器的名称。

exec sp_rename dz_insert,dz_insert_2

[20210107201921852.png]: /images/20221119/cdf64c72cd854a51b9c2af0d787a3f32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDE5Nzc0OQ_size_16_color_FFFFFF_t_70]: /images/20221119/70fd673ce7c148c6a7d224ddd9516611.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDE5Nzc0OQ_size_16_color_FFFFFF_t_70 1]: /images/20221119/9c94e7869cde42299e3c31d2d5e304a2.png