您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 《SQL Server 2008从入门到精通》--20180717

《SQL Server 2008从入门到精通》--20180717

发布时间:2019-10-15 03:28编辑:威尼斯官方网站登录浏览(199)

    目录

    • 1.触发器
      • 1.1.DDL触发器
      • 1.2.DML触发器
      • 1.3.创建触发器
        • 1.3.1.创建DML触发器
        • 1.3.2.创建DDL触发器
        • 1.3.3.嵌套触发器
        • 1.3.4.递归触发器
      • 1.4.管理触发器

    1.触发器

    触发器是一种特殊的存储过程,与表紧密关联。

    1.1.DDL触发器

    当服务器或数据库中发生数据定义语言(DDL)事件时将被调用。如CREATE,ALTERDROP等操作。如果要执行以下操作,可以使用DDL触发器:

    • 防止对数据库架构进行更改
    • 希望数据库中发生某些情况以响应数据库架构中的更改
    • 要记录数据库架构中的更改或事件

    1.2.DML触发器

    当数据库服务器中发生数据操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,如果检测到错误,则整个事务回滚。DML触发器在一下方面非常有用:

    • 可实现数据库相关表之间的级联更改
    • 可以防止恶意或错误的DML语句事件,并强制执行比CHECK约束更为复杂的其他限制
    • 可以评估数据修改前后表的状态,并根据该差异采取措施

    一个表中的多个同类DML触发器,允许用多个不同的操作来响应同一个修改语句
    SQL Server 2008为每个触发器创建了2个特殊的表:INSERTED表和DELETED表。这是两个逻辑表,由系统来创建和维护,用户不能对他们进行修改。它们存放在内存中,而不是在数据库中,并且结构与被DML触发器作用的表的结构相同。
    INSERTED表中存放了由执行INSERTUPDATE语句而插入的所有行,在执行INSERTUPDATE语句时,新的行将同时被插入到触发器作用的表和INSERTED表中。INSERTED表中的行是触发器作用的表中行的副本。
    DELETED表中存放了由执行DELETEUPDATE语句而删除的所有行,在执行DELETEUPDATE语句时,被删除的行将由触发器作用的表中被移动到DELETED表,两个表中不会有重复行。

    1.3.创建触发器

    1.3.1.创建DML触发器

    1.3.1.1.INSERT触发器

    示例1:创建一个触发器Automatic_division,当在Student表中插入一条学生信息时,触发器根据入学分数(stu_enter_score)对学生进行自动分班,并在class_student表中插入一条记录。
    分班要求:
    |Stu_enter_score |Class_id |Class_name|
    |-------------------|------------------|--------------|
    |stu_enter_score>=700| 01| 创新A班|
    |650<=Stu_enter_score<700| 02| 重点B班|
    |600<=Stu_enter_score<650| 03| 提高C班|
    |550<=Stu_enter_score<600| 04| 普通D班|
    |500<=Stu_enter_score<550| 05| 普通E班|
    |Stu_enter_score<500| 06| 普通F班|
    执行下列语句

    CREATE TRIGGER automatic_division
    ON student--新建一个检测student表的触发器,命名automatic_division
    FOR INSERT--检测到INSERT操作时触发器工作
    AS
    DECLARE @score INT,@stu_no VARCHAR(8),@class_id CHAR(2)
    --声明三个变量
    DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY--声明一个指向inserted表的局部游标stu_cursor
    FOR SELECT stu_no,stu_enter_score FROM inserted
    OPEN stu_cursor--打开游标
    FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标指向inserted表的第一个数据并把游标指向的stu_no和stu_enter_score值分别赋值给@stu_no和@score
    WHILE @@FETCH_STATUS=0--开始循环
    BEGIN
    BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
    IF @score>=700
    SET @class_id='01'
    ELSE IF @score<700 AND @score>=650
    SET @class_id='02'
    ELSE IF @score<650 AND @score>=600
    SET @class_id='03'
    ELSE IF @score<600 AND @score>=550
    SET @class_id='04'
    ELSE IF @score<550 AND @score>=500
    SET @class_id='05'
    ELSE
    SET @class_id='06'
    END
    --判断结束
    INSERT INTO class_student(class_id,stu_no)
    VALUES(@class_id,@stu_no)--将数据插入到class_student表中
    FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
    END--循环结束
    CLOSE stu_cursor--关闭游标
    DEALLOCATE stu_cursor--释放游标资源
    GO
    

    验证代码是否正确
    student表中插入数据,并查看class_student表中的数据是否正确

    INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
    VALUES('20180001','邹莉莉','女','389'),
    ('20180002','万兴','男','701'),
    ('20180003','孙伟','男','652'),
    ('20180004','温佳静','女','676'),
    ('20180005','姜立夫','男','542')
    

    Class_student表中的数据如图所示
    图片 1
    游标示例2:对student表中还未分班的学生进行分班
    Student表中的数据如图所示
    图片 2
    其中stu_no20180001~20180005的学生已经在示例1中分班,剩下的学生全都未分班。
    执行下列语句

    ALTER TABLE student
    ADD stu_division_state bit--为student表新建一列记录是否已分班,true表示已分班
    GO
    DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM class_student
    --新建游标stu_class_cursor指向class_student表的所有数据
    OPEN stu_class_cursor--打开游标
    DECLARE @stu_no VARCHAR(8)
    FETCH NEXT FROM stu_class_cursor INTO @stu_no
    WHILE @@FETCH_STATUS=0
    BEGIN
    UPDATE student
    SET stu_division_state=1
    WHERE stu_no=@stu_no
    FETCH NEXT FROM stu_class_cursor INTO @stu_no
    END
    CLOSE stu_class_cursor--关闭游标
    DEALLOCATE stu_class_cursor--释放游标资源
    ---所有学生是否分班已经全部记录在stu_division_state中
    GO
    DECLARE @stu_no VARCHAR(8),@score INT,@class_id CHAR(2)
    DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state IS NULL
    --新建student表的游标stu_cursor,指向所有未分班学生
    OPEN stu_cursor--打开游标
    FETCH NEXT FROM stu_cursor INTO @stu_no,@score
    WHILE @@FETCH_STATUS=0--循环开始
    BEGIN
    BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
    IF @score>=700
    SET @class_id='01'
    ELSE IF @score<700 AND @score>=650
    SET @class_id='02'
    ELSE IF @score<650 AND @score>=600
    SET @class_id='03'
    ELSE IF @score<600 AND @score>=550
    SET @class_id='04'
    ELSE IF @score<550 AND @score>=500
    SET @class_id='05'
    ELSE
    SET @class_id='06'
    END
    INSERT INTO class_student(class_id,stu_no)
    VALUES(@class_id,@stu_no)--将数据插入到class_student表中
    UPDATE student--将student表的stu_division_state改成已分班
    SET stu_division_state=1
    WHERE stu_no=@stu_no
    FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
    END--循环结束
    CLOSE stu_cursor
    DEALLOCATE stu_cursor
    GO
    

    结果如图所示
    Student表的数据
    图片 3
    Class_student表的数据
    图片 4
    至此Student表中所有学生都已分班
    为了以后方便,可以将游标示例2中的代码稍作修改封装成一个用户自定义存储过程
    存储过程示例3
    修改后的代码如下

    CREATE PROCEDURE student_division
    AS
    BEGIN
    UPDATE student
    SET stu_division_state=0--先将student表中所有学生的分班情况都标成未分班
    
    DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM class_student
    --新建游标stu_class_cursor指向class_student表的所有数据
    OPEN stu_class_cursor--打开游标
    DECLARE @stu_no VARCHAR(8)
    FETCH NEXT FROM stu_class_cursor INTO @stu_no
    WHILE @@FETCH_STATUS=0
    BEGIN
    UPDATE student
    SET stu_division_state=1
    WHERE stu_no=@stu_no--利用游标找出student表中已分班的学生并标记分班状态
    FETCH NEXT FROM stu_class_cursor INTO @stu_no
    END
    CLOSE stu_class_cursor--关闭游标
    DEALLOCATE stu_class_cursor--释放游标资源
    ---所有学生是否分班已经全部记录在stu_division_state中
    
    DECLARE @score INT,@class_id CHAR(2)
    DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state=0
    --新建student表的游标stu_cursor,指向所有未分班学生
    OPEN stu_cursor--打开游标
    FETCH NEXT FROM stu_cursor INTO @stu_no,@score
    WHILE @@FETCH_STATUS=0--循环开始
    BEGIN
    BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
    IF @score>=700
    SET @class_id='01'
    ELSE IF @score<700 AND @score>=650
    SET @class_id='02'
    ELSE IF @score<650 AND @score>=600
    SET @class_id='03'
    ELSE IF @score<600 AND @score>=550
    SET @class_id='04'
    ELSE IF @score<550 AND @score>=500
    SET @class_id='05'
    ELSE
    SET @class_id='06'
    END
    INSERT INTO class_student(class_id,stu_no)
    VALUES(@class_id,@stu_no)--将数据插入到class_student表中
    UPDATE student--将student表的stu_division_state改成已分班
    SET stu_division_state=1
    WHERE stu_no=@stu_no
    FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
    END--循环结束
    CLOSE stu_cursor
    DEALLOCATE stu_cursor
    END
    GO
    

    注:和游标示例2的代码相比,示例3的代码添加了将所有学生分班状态标记为0的过程,去掉了添加stu_division_state列的过程,但对原来已有的学生的分班状态赋值这个步骤并未删去,而是进行重复校验。并且删除了两段代码中的GO和第二段用于给学生分班的代码中对@stu_no变量的重复声明。

    student表插入数据并运行student_division的存储过程

    注:对student表插入数据前应先禁用示例1的触发器automatic_division

    执行下列语句

    ALTER TABLE student DISABLE TRIGGER automatic_division
    --禁用automatic_division触发器
    INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score,stu_division_state)
    VALUES('20180006','王洋','男','724',NULL),
    ('20180007','易阳','男','713',NULL),
    ('20180008','孙浩','男','584',NULL),
    ('20180009','张秋燕','女','420','False'),
    ('20180010','胡燕','女','527','True')
    

    Student表的数据如图所示,红框内就是我刚刚插入还未分班的数据,其中2018000920180010这两个学生的分班状态被我误标成FalseTrue
    图片 5
    执行存储过程

    EXEC dbo.student_division
    

    结果如图所示
    Student表的数据(分班状态都为true了)
    图片 6
    Class_student表的数据
    图片 7

    1.3.1.2.DELETE触发器

    当针对目标数据库运行DELETE语句时就会激活DELETE触发器。用户直接运行DELETE语句和使用DELETE触发器又有所不同,当激活DELETE触发器后,从受触发器影响的表中删除的行会被放置在一个特殊的临时表——DELETED表中。DELETED表还允许引用由初始化DELETE语句产生的日志数据。
    DELETE触发器被激活时,需要考虑以下几点

    • 当某行被添加到DELETED表中时就不存在于数据库表,因此数据库表和DELETED表不可能存在相同行。
    • 系统自动创建DELETED表时,空间从内存中分配。DELETED表被存储在高速缓存中。
    • DELETE操作定义的触发器并不执行TRUNCATE TABLE语句,原因在于日志不记录TRUNCATE TABLE语句。

    示例4:为student表定义一个DELETE触发器,当删除一条学生信息时,class_student表中该学生的分班信息也会被删除
    执行下面的语句

    CREATE TRIGGER delete_student
    ON student
    FOR DELETE
    AS
    DECLARE @stu_no VARCHAR(8)
    DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM deleted
    OPEN stu_cursor
    FETCH NEXT FROM stu_cursor INTO @stu_no
    WHILE @@FETCH_STATUS=0
    BEGIN
    DELETE FROM class_student
    WHERE stu_no=@stu_no
    FETCH NEXT FROM stu_cursor INTO @stu_no
    END
    CLOSE stu_cursor
    DEALLOCATE stu_cursor
    GO
    

    测试delete_student触发器的正确性
    Student表的数据如图所示
    图片 8
    Class_student表的数据如图所示
    图片 9
    执行下列语句

    DELETE FROM student
    WHERE stu_enter_score<=351
    --在student表中删除入学成绩小于分的学生
    

    student表来看,只有入学编号为2018001120180012的学生成绩被删除。该操作激活了delete_student触发器
    Class_student表的数据如图所示
    图片 10
    入学编号为2018001120180012的学生分班信息已经从class_student表中自动删除。

    1.3.1.3.UPDATE触发器

    当针对目标数据库运行UPDATE语句时就会激活UPDATE触发器。对UPDATE触发器来说,临时表INSERTEDDELETED依然有用。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来确定是否更新了多行和如何执行触发器动作。
    Student表的数据如图所示
    图片 11
    Class_student表的数据如图所示
    图片 12
    示例5:当student表中的stu_no字段更新时,同步更新class_student表中的stu_no字段
    执行下列语句新建触发器update_stu_no_single

    CREATE TRIGGER update_stu_no_single
    ON student
    FOR UPDATE
    AS
    IF UPDATE(stu_no)
    BEGIN
    UPDATE class_student
    SET stu_no=(SELECT stu_no FROM inserted)
    WHERE stu_no=(SELECT stu_no FROM deleted)
    END
    GO
    

    验证update_stu_no_single触发器是否正确,在Student表中执行下列语句,将student表中stu_no为“20180101”的学生的stu_no改成00000000

    UPDATE student
    SET stu_no='00000000'
    WHERE stu_no='20180101'
    

    执行成功后,update_stu_no_single触发器被激活,class_student表的数据如图所示
    图片 13

    注:update_stu_no_single触发器只能对单行记录的UPDATE操作起效,如果批量UPDATE stu_no,执行语句时会提示子查询返回的值不止1个。下面的示例6将提供批量UPDATE stu_no的触发器

    示例6:实现当student表的stu_no字段批量更新时,class_student表的stu_no也同步批量更新
    首先将student表和class_student表的数据修改成原来的样子,并且删除update_stu_no_single触发器
    Student表的数据如图所示
    图片 14
    Class_student表的数据如图所示
    图片 15
    执行下列语句新建触发器update_stu_no_batch

    CREATE TRIGGER update_stu_no_batch
    ON student
    FOR UPDATE
    AS
    DECLARE @stu_no_insert VARCHAR(8),@stu_no_delete VARCHAR(8)
    DECLARE stu_cursor_insert CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM inserted
    OPEN stu_cursor_insert
    DECLARE stu_cursor_delete CURSOR LOCAL FORWARD_ONLY
    FOR SELECT stu_no FROM deleted
    OPEN stu_cursor_delete
    FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
    FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
    WHILE @@FETCH_STATUS=0
    BEGIN
    UPDATE class_student
    SET stu_no=@stu_no_insert
    WHERE stu_no=@stu_no_delete
    FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
    FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
    END
    CLOSE stu_cursor_insert
    CLOSE stu_cursor_delete
    DEALLOCATE stu_cursor_insert
    DEALLOCATE stu_cursor_delete
    GO
    

    验证update_stu_no_batch触发器的准确性,对student表执行下列语句,实现批量修改操作

    UPDATE student
    SET stu_no='00000000'
    WHERE stu_no LIKE '201801%'
    GO
    

    Student表的数据如图所示
    图片 16
    Class_student表的数据如图所示
    图片 17
    我们再来验证update_stu_no_batch触发器对更新单行stu_no数据是否有效。将student表class_student表的数据改回原来的样子,然后执行下列语句

    UPDATE student
    SET stu_no='00000000'
    WHERE stu_no='20180101'
    

    Class_student表的数据如图所示
    图片 18

    注:在将表数据改成原来的样子时,直接在编辑前200行中操作或者用T-SQL语句操作,对student表数据操作,不成功的话要考虑受键和约束的影响,对class_student表数据操作,不成功的话要考虑受触发器影响。

    1.3.1.4.INSTEAD OF触发器

    INSTEAD OF触发器可以指定执行触发器,而不是执行触发SQL语句,从而屏蔽原来的SQL语句,而转向执行触发器内部的语句。每个表或者视图只能有1个INSTEAD OF触发器。INSTEAD OF触发器的特点是,能够使作为触发条件的SQL语句不执行。
    Membership表的数据如图所示
    图片 19
    Call_slip表的数据如图所示
    图片 20
    示例7:对LibraryManagement数据库里的membership表写一个防删除触发器,尚有借书未还的读者无法被删除
    执行下列语句创建member_delete_single触发器

    CREATE TRIGGER member_delete_single
    ON membership
    INSTEAD OF DELETE
    AS
    BEGIN
    IF NOT EXISTS(SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还')
    DELETE FROM membership WHERE member_id=(SELECT member_id FROM deleted)
    ELSE
    BEGIN
    SELECT '该用户尚有图书未还,无法删除'
    SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还'
    END
    END
    GO
    

    验证触发器的正确性,执行下列语句

    DELETE FROM membership
    WHERE member_id='20060128'
    

    结果如图所示
    图片 21
    该触发器只针对DELETE一条数据有效
    示例8:对LibraryManagement数据库里的membership表写一个防批量删除触发器,尚有借书未还的读者无法被删除
    Membership表的数据如图所示
    图片 22
    Call_slip表的数据如图所示
    图片 23
    执行下列语句新建触发器(将示例7中的member_delete_single触发器先删除)

    CREATE TRIGGER member_delete_batch
    ON membership
    INSTEAD OF DELETE
    AS
    BEGIN
    DECLARE member_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT member_id FROM deleted
    OPEN member_cursor
    DECLARE @member_id VARCHAR(8)
    FETCH NEXT FROM member_cursor INTO @member_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    BEGIN
    IF NOT EXISTS(SELECT* FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')
    DELETE FROM membership WHERE member_id=@member_id
    ELSE
    PRINT '用户' @member_id '无法删除'
    END
    FETCH NEXT FROM member_cursor INTO @member_id
    END
    CLOSE member_cursor
    DEALLOCATE member_cursor
    END
    GO
    

    结果如图所示
    图片 24
    Membership表的数据如图所示
    图片 25
    示例9:对LibraryManagement数据库里的call_slip表写一个防超借触发器,一个读者的未还图书最多只能有5本,超出不能再借(这里还是针对批量处理数据创建触发器)
    Call_slip表的数据如图所示
    图片 26
    执行下列语句创建provent_overborrowing_batch触发器

    CREATE TRIGGER provent_overborrowing_batch
    ON call_slip
    INSTEAD OF INSERT
    AS
    BEGIN
    DECLARE @member_id VARCHAR(8)
    DECLARE borrow_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT member_id FROM inserted
    OPEN borrow_cursor
    FETCH NEXT FROM borrow_cursor INTO @member_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    BEGIN
    IF (SELECT COUNT(*) FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')<5
    INSERT INTO call_slip SELECT * FROM inserted WHERE member_id=@member_id
    ELSE
    PRINT '用户' @member_id '已借阅且未还的图书超过5本,无法再借'
    END
    FETCH NEXT FROM borrow_cursor INTO @member_id
    END
    END
    GO
    

    执行下列语句测试provent_overborrowing_batch触发器的正确性,其中member_id为“20060128”的用户借书未还超过5本,应该是无法再借的。

    --测试数据
    INSERT INTO call_slip(book_id,member_id,loan_period,borrow_state)
    VALUES('20130002','20060128','30','未归还'),
    ('20130001','20060128','20','未归还'),
    ('20130003','20060128','30','未归还'),
    ('20130004','20062919','30','未归还'),
    ('20130005','20150821','45','未归还')
    

    结果如图所示
    图片 27
    Call_slip表的数据如图所示,红框里是新插入的数据
    图片 28

    1.3.2.创建DDL触发器

    DDL触发器只为了响应CREATEDROPALTER事件而激活,它的作用域是整个数据库或者服务器,而不是作用域某张表或试图。它可以有效控制哪位用户可以修改数据库结构以及如何修改。
    示例10:创建一个DDL触发器,控制上班时间(8:00-18:00)不能对LibraryManagement数据库表和试图结构进行新建,修改和删除操作。
    执行下列语句创建触发器deny_DDL_table

    CREATE TRIGGER deny_DDL_table
    ON DATABASE
    WITH ENCRYPTION
    FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
    AS
    DECLARE @eventdata XML
    SET @eventdata=EVENTDATA()
    IF(DATEPART(HOUR,GETDATE()) BETWEEN 8 AND 17)
    BEGIN
    SELECT '触发器deny_DDL_table已禁止工作时间8:00-18:00对LibraryManagement数据库的CREATE,ALTER,DROP操作'
    SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType,--事件类型
    @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime,--时间触发的时间
    @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName,--数据库名字
    @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') AS ObjectName,--操作的对象名称
    @eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') AS ObjectType,--操作的对象类型
    @eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText--操作命令文本
    ROLLBACK---对操作进行回滚,也可以不回滚
    END
    GO
    

    执行以下代码以测试DDL触发器deny_DDL_table的正确性

    USE LibraryManagement
    CREATE TABLE test(
    t_id VARCHAR(2),
    t_name VARCHAR(20)
    )
    

    结果如图所示
    图片 29
    图片 30

    注:EVENTDATA()可在触发器内部使用,返回有关数据库和服务器事件的信息,以XML格式返回。只有直接在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会返回数据。如果EVENTDATA由其他例程调用(即使这些例程由DDL或登录触发器进行调用),将返回 NULL

    1.3.3.嵌套触发器

    1.3.3.1.嵌套触发器

    如果一个触发器在执行操作时引发了另一个触发器,而这个触发器又引发了下一个触发器,那么这些触发器就是嵌套触发器。嵌套触发器在安装时就被启用,但是可以使用sp_configure存储过程禁用和重新启用嵌套。
    DML触发器和DDL触发器最多可以嵌套32层,可以通过nested triggers来配置是否可以嵌套AFTER触发器,但是不管此设置如何都可以嵌套INSTEAD OF触发器。如果嵌套触发器进入了无限循环,该触发器将被终止,并且回滚整个事务。嵌套触发器具有多种用途,比如保存前一个触发器所影响的行的副本。
    使用嵌套触发器时应该注意以下几点:

    • 默认情况下,嵌套触发器配置选项开启。
    • 在同一个触发器事务中,一个触发器不会被触发两次,触发器不会调用他自己来响应触发器中对同一个表的第二次更新
    • 由于触发器是一个事务,一旦嵌套中任何一层的触发器出现错误,将回滚整个事务。

    示例11:有teacher_course表(教师所教课程表),course表(课程表)和course_selection表(学生选课表),写一个嵌套触发器,实现课程取消后,删除教师所教课程表中关于该课程的记录,而教师所教课程表中该课程的记录被取消,导致该课程的学生选课记录也做相应取消。
    执行下列语句

    --创建course表上的触发器,删除course表中的课程,teacher_course表中的记录做对应删除
    CREATE TRIGGER course_delete_batch
    ON course
    FOR DELETE
    AS
    DECLARE @course_id CHAR(4)
    DECLARE course_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT course_id FROM deleted
    OPEN course_cursor
    FETCH NEXT FROM course_cursor INTO @course_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    DELETE FROM teacher_course WHERE course_id=@course_id
    FETCH NEXT FROM course_cursor INTO @course_id
    END
    GO
    --创建teacher_course表上的触发器,删除教师课程表的记录,学生选课表的记录也做对应删除
    CREATE TRIGGER teacher_course_delete_batch
    ON teacher_course
    FOR DELETE
    AS
    DECLARE @course_id CHAR(4)
    DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT course_id FROM deleted
    OPEN teacher_course_cursor
    FETCH NEXT FROM teacher_course_cursor INTO @course_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
    DELETE FROM course_selection WHERE course_id=@course_id
    ELSE
    PRINT 'course_id为' @course_id '的课程依然正常开课,该课程的学生选课情况不予删除'
    FETCH NEXT FROM teacher_course_cursor INTO @course_id
    END
    GO
    

    course_delete_batch和** teacher_course_delete_batch就形成了一个嵌套触发器,下面来验证嵌套触发器的正确性。 Course表中的数据如图所示
    图片 31
    Teacher_course表中的数据如图所示
    图片 32
    Course_selection**表中的数据如图所示
    图片 33
    以课程0013为例,执行下列语句

    DELETE FROM course WHERE course_id='0013'
    

    Course表的数据如图所示
    图片 34
    Teacher_course表的数据如图所示
    图片 35
    Course_selection表的数据如图所示
    图片 36
    所有关于0013课程的数据都被删除。嵌套触发器有效。

    注:在触发器teacher_course_delete_batch中,我额外加入了一个判断,当teacher_course表中还有老师在教授这门课程时,所有关于这门课程的学生选课信息都不予删除。这样做在嵌套触发器里是多余的,删除一门课程,必然会删除teacher_course表中所有与这门课程有关的记录,也必然删除course_selection表中所有与这门课程有关的记录,但是,这样做可以保证该触发器能够独立于嵌套触发器被单独激活。Teacher_course_delete_batch触发器还能用于其他嵌套触发器中,看示例12

    示例12:有teacher表(教师信息表),teacher_course(教师所教课程表),和course_selection表(学生选课记录表),写一个嵌套触发器,实现当一个教师离职时,在删除该教师所教课程信息,如果没有教师教这门课程,再删除该课程选课记录。
    其中teacher_course表的触发器teacher_course_delete_batch已经在示例11中写完,只需创建teacher表的teacher_delete_batch触发器即可
    执行下列代码

    CREATE TRIGGER teacher_delete_batch
    ON teacher
    FOR DELETE
    AS
    DECLARE @teacher_id CHAR(4)
    DECLARE teacher_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT teacher_id FROM deleted
    OPEN teacher_cursor
    FETCH NEXT FROM teacher_cursor INTO @teacher_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    DELETE FROM teacher_course WHERE teacher_id=@teacher_id
    FETCH NEXT FROM teacher_cursor INTO @teacher_id
    END
    GO
    

    测试嵌套触发器的正确性
    Teacher表的数据如图所示
    图片 37
    Teacher_course表的数据如图所示
    图片 38
    Course_selection表的数据如图所示
    图片 39
    以删除0012号教师路易为例,0012号教师教授0013号课程,且teacher_course表中并无其他教师教授0013号课程,按照逻辑要删除teacher_course表中0012号教师的所教课程记录和course_selection表中所有0013号课程的选课记录。执行下列语句

    DELETE FROM teacher WHERE teacher_id='0012'
    

    Teacher表的数据如图所示
    图片 40
    Teacher_course表的数据如图所示
    图片 41
    Course_selection表的数据如图所示
    图片 42
    测试结果正确
    参照上面的数据,继续测试另一种情况,以删除0011号教师卢含笑为例,0011号教师教授0012号课程,在teacher_course表中还有其他教师教授该课程,因此嵌套触发器会删除teacher_course表中关于0011号教师教授课程记录,但不会删除course_selection表中关于0012号课程的选课记录。执行下列语句

    DELETE FROM teacher WHERE teacher_id='0011'
    GO
    

    结果如图所示
    图片 43
    Teacher表的数据如图所示
    图片 44
    Teacher_course表的数据如图所示
    图片 45
    Course_selection表的数据如图所示
    图片 46

    1.3.3.2.查看触发器嵌套的层数

    可以使用@@NESTLEVEL全局变量来查看当前触发器嵌套的层数
    示例13:在示例11teacher_course_delete_batch触发器中利用@@NESTLEVEL全局变量查看当前触发器嵌套的层数
    执行下列语句修改teacher_course_delete_batch触发器

    ALTER TRIGGER teacher_course_delete_batch
    ON teacher_course
    FOR DELETE
    AS
    DECLARE @course_id CHAR(4)
    DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT course_id FROM deleted
    OPEN teacher_course_cursor
    FETCH NEXT FROM teacher_course_cursor INTO @course_id
    WHILE @@FETCH_STATUS=0
    BEGIN
    IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
    DELETE FROM course_selection WHERE course_id=@course_id
    ELSE
    PRINT 'course_id为' @course_id '的课程依然正常开课,该课程的学生选课情况不予删除'
    FETCH NEXT FROM teacher_course_cursor INTO @course_id
    SELECT @@NESTLEVEL AS NESTLEVEL
    END
    GO
    

    测试teacher_course_delete_batch触发器(数据就不看了,未影响触发器原来的功能)
    执行下列语句

    DELETE FROM teacher_course WHERE teacher_id='0009'
    --直接在teacher_course表中删除,激活teacher_course_delete_batch触发器
    

    结果如图所示
    图片 47
    执行下列语句

    DELETE FROM teacher WHERE teacher_id='0009'
    --在teacher表中删除,触发teacher_delete_batch触发器,进而触发teacher_course_delete_batch触发器
    

    结果如图所示
    图片 48

    1.3.3.3.禁用和启用嵌套触发器

    EXEC sp_configure 'nested triggers',0;
    GO
    --禁用嵌套触发器
    EXEC sp_configure 'nested triggers',1;
    GO
    --启用嵌套触发器
    

    1.3.4.递归触发器

    1.3.4.1.递归触发器

    触发器被激活,更改了表中数据,这种更改又激活了它自己,这种触发器被称为递归触发器。数据库创建时默认递归触发器禁用。但可以使用ALTER DATABASE选项来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用状态,如果嵌套触发器禁用,不管递归触发器的配置是什么都将被禁用。而在递归触发器中,inserted表和deleted表都只包含被上一次触发器影响的行数据。
    递归触发器有以下两种不同类型(这边没有合适的应用示例可举,先不举例了)

    1.3.4.2.直接递归

    直接递归触发器是指整个递归过程只有它本身一个触发器的参与。自己激活了自己。

    1.3.4.3.间接递归

    间接递归触发器是指整个递归过程有多个触发器参与,例如A激活B,B激活C,C激活A。可以看成是递归和嵌套的结合。
    使用递归触发器时需要注意以下几点:
    递归触发器很复杂,需要经过有条理的设计和全面测试
    在任意点的数据修改都会激活递归触发器。只能按触发器被激活的特定顺序更新表。
    所有触发器一起构成一个大事务,任意触发器的任意位置上的ROLLBACK语句都将取消所有数据的输入,所有数据均被擦除。
    触发器最多只能递归16层,一旦有第17个触发器参与进来,结果与ROLLBACK命令一样,所有数据都将被擦除

    1.3.4.4.启用递归触发器

    可以使用SQL Server 2008的管理器工具来启用递归触发器。
    图片 49

    1.4.管理触发器

    禁用和启用触发器
    执行下列语句禁用和启用触发器

    ALTER TABLE student DISABLE TRIGGER update_stu_no_single
    --禁用update_stu_no_single触发器
    GO
    ALTER TABLE student ENABLE TRIGGER update_stu_no_single
    --启用update_stu_no_single触发器
    GO
    

    执行下列语句禁用和启用数据库级别触发器

    DISABLE TRIGGER deny_DDL_table ON DATABASE
    --禁用数据库级别触发器deny_DDL_table
    GO
    ENABLE TRIGGER deny_DDL_table ON DATABASE
    --启用数据库级别触发器deny_DDL_table
    GO
    

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:《SQL Server 2008从入门到精通》--20180717

    关键词: