触发器--特殊的存储过程

 很多人应该都用过存储过程和触发器，而触发器并不是一个新的概念,它只是一个特殊一些的存储过程, 它可以根据执行的TQL 语句自动执行,不需要我们去调用。

           触发器是为了数据库表结构的完成性、一致性而使用的，和单个表的约束一样,触发器是对于表之间的约束,它比主键约束范围更广泛一些,作用范围更大一些,下面给大家介绍一下常用的三种触发器.

 

           【插入触发器(Insert Trigger)】

           当向表里面插入记录时候触发

           通过临时表（Inserted）来实现，我们需要往表里插入一条记录时，插入的记录先插入到临时表中，然后，从临时表插入到目标表中，为什么采用临时表，我觉的是因为触发器是自动触发，既然是自动触发就不会往里面传参数，那么参数怎么传入到目标表中呢，为了传入参数加入了临时表，将要插入或者删除的记录先放到了临时表中，再从临时表插入数据。

 

          下面是一个校区的基本表TB_Campus，向表里插入数据时触发该触发器，自动向该小区下的建筑表TB_Building添加数据记录，从而保证这两个表的数据一致性。

           触发器通常是为了两个表或多个表中数据一致加入的。

          例如：

[sql] view plaincopyprint?

<SPAN style="FONT-SIZE: 18px">-- =============================================
-- Author:      评教小组-李龙生
-- Create date: 2012年12月29日15:02:06
-- Description: 添加校区记录触发器
-- =============================================
CREATE TRIGGER [dbo].[TR_Campus_Add]
on [dbo].[TB_Campus]

INSTEAD OF INSERT

AS
    DECLARE
    @BuildingID VARCHAR(50),             --建筑物ID
    @BuildingName VARCHAR(50),           --建筑名
    @BuildingCode VARCHAR(50),           --建筑代码
    @Descriptions VARCHAR(500),          --建筑描述
    @Operator   VARCHAR(50)              --操作人

BEGIN

    --获取要插入记录的标记号（建筑）
    SELECT @BuildingID=BuildingID  from <SPAN style="COLOR: #3333ff"><STRONG>inserted  --插入临时表
</STRONG></SPAN>
    --插入基本表中的信息（建筑表）
        INSERT INTO  TB_Building(BuildingID,BuildingCode,BuildingName,Descriptions,Operator)
    VALUES(@BuildingID，@BuildingCode,@BuildingName,@Descriptions,@Operator)

END</SPAN>

 

           【更新触发器(Update Trigger)】

            当更新表中的任意一个字段时触发

            更新触发器需要用到两个表，一个是Inserted 表，另一个是Deleted表，Inserted表存储更新后的记录，Deleted表存储更新后的记录，更新一个表示需要先将要删除的记录放入Deleted表中，然后，将Inserted表的新记录插入表中，从而实现更新操作。

 

            例如：下面是教务系统中一个触发器，更新老师课程关系表（TR_TeachCourseLink）记录，并更新与这个表有关的表。

 

            在更新、删除触发器中我们需要注意一个问题，触发器为什么会触发呢，它是基于SQL 语句而执行的，但更新、删除语句可以一次性删除多条记录，其实SQL 语句只执行了一次，即触发器也只执行一次，那么当涉及到表关系为一对多或者多对多时，就会出现更新或删除不完下面表中记录

          

            为了解决这一问题，我们在下面存储过程中加入了游标，使得更新或删除记录时一条一条的删除，每删除一条，触发器就会执行一次，使得数据保持一致，不过，这样可能大大降低了SQL语句执行速度，如果数据量很大，速度会变慢。

 

[sql] view plaincopyprint?

<SPAN style="FONT-SIZE: 18px">USE [BasicDataSystem]
GO
/****** Object:  Trigger [dbo].[TR_TeachCourseLink_US]    Script Date: 2013/1/2 19:28:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      评教小组-李龙生
-- Create date:      2012年12月30日21:03:26
-- Description:  假删除授课表中课表记录
--                   授课课程与虚拟班关系、虚拟班
--           授课课程与上课班学生关系、上课班学生
--                   课程与选修课学生关系、选修课学生
-- =============================================
ALTER TRIGGER [dbo].[TR_TeachCourseLink_US]

    ON [dbo].[TBR_TeachCourseLink]

    AFTER Update
AS

    DECLARE
    @err1 INT,                   --存储错误号
    @err2 INT,
    @err3 INT,
    @err4 INT,
    @err5 INT,
    @err6 INT,
    @err7 INT,
    @Indexing INT,               --授课课程主键
    @TeachClassID VARCHAR(36),   --虚拟班主键
    @StudentID VARCHAR(36)       --学生主键

BEGIN

    --选择要删除授课课程记录的主键
    SELECT @Indexing=Indexing FROM  <SPAN style="COLOR: #3333ff"><STRONG>deleted    --删除临时表
</STRONG></SPAN>
    --开启事务
    BEGIN TRANSACTION

        /*更新授课课程记录、授课课程与虚拟班关系、虚拟班，各表中的IsAvailable
        字段为 “否”*/

        --更新授课课程记录 IsAvailable='否'
        UPDATE TBR_TeachCourseLink SET IsAvailable='否' WHERE Indexing=@Indexing
        SET @err1=@@ERROR

        --更新虚拟班与TeachCourse表的关系 IsAvailable='否'
        UPDATE TBR_CourseTeachClassLink SET IsAvailable='否' WHERE Indexing=@Indexing
        SET @err2=@@ERROR

        --更新虚拟班表 IsAvailable='否'(需要触发多条记录，用游标操作)
<SPAN style="COLOR: #cc33cc"><STRONG>       --声明游标
        DECLARE TeachClassCursor CURSOR FOR
        SELECT TeachClassID FROM TBR_CourseTeachClassLink  WHERE Indexing=@Indexing
        --打开游标
            OPEN TeachClassCursor
                FETCH NEXT FROM TeachClassCursor INTO @TeachClassID  --给变量赋初始值
                WHILE @@FETCH_STATUS=0 --语句执行成功
                    BEGIN
                    --更新中的记录
                    Update TB_TeachClass SET isAvailable = '否' WHERE TeachClassID=@TeachClassID
                    FETCH NEXT FROM TeachClassCursor INTO @TeachClassID
                    END
            --关闭并释放游标
            CLOSE TeachClassCursor
        DEALLOCATE TeachClassCursor
</STRONG></SPAN>        SET @err3=@@ERROR

        /*更新授课课程与选修课学生关系、学生表，各表中的IsAvailable
        字段为 “否”*/
        --更新授课课程与选修课学生的关系表IsAvailable='否'
        UPDATE TBR_StuChooseCourseLink SET IsAvailable='否' WHERE Indexing=@Indexing
        SET @err6=@@ERROR

        --更新学生表，将IsAvailable字段变为'否'(需要触发多条记录，用游标操作)
        --声明游标
        DECLARE StudentCursor CURSOR FOR
        SELECT StudentID FROM TBR_StuChooseCourseLink  WHERE Indexing=@Indexing
        --打开游标
            OPEN StudentCursor
                    FETCH NEXT FROM StudentCursor INTO @StudentID  --给变量赋初始值
                    WHILE @@FETCH_STATUS=0 --语句执行成功
                    begin
                    --更新中的记录
                    Update TB_Student SET isAvailable = '否' WHERE StudentID=@StudentID
                    FETCH NEXT FROM StudentCursor INTO @StudentID
                    end
            --关闭并释放游标
            CLOSE StudentCursor
        DEALLOCATE StudentCursor
        SET @err7=@@ERROR

    --判断是否执行成功
    IF (@err1 =0 and @err2=0 and @err3=0 and @err4=0 and @err5=0 and @err6=0 and @err7=0)
        --提交事务
        COMMIT TRANSACTION
    ELSE
        --事务回滚
        ROLLBACK TRANSACTION


END</SPAN>

 

           【删除触发器(Delete Trigger)】

            当删除表中记录时触发

            先把要删除的记录放到Deleted临时表中，然后，再删除。

             例如：

[sql] view plaincopyprint?

<SPAN style="FONT-SIZE: 18px">USE [BasicDataSystem]
GO
/****** Object:  Trigger [dbo].[TR_TeachCourseLink_D]    Script Date: 2013/1/2 19:29:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      评教小组-李龙生
-- Create date:      2012年12月30日21:03:26
-- Description: 彻底删除授课表中课表记录
--                   课程与选修课学生关系、选修课学生
-- =============================================
ALTER TRIGGER [dbo].[TR_TeachCourseLink_D]

    ON [dbo].[TBR_TeachCourseLink]

    INSTEAD OF DELETE
AS

    DECLARE
    @err1 INT,                   --存储错误号
    @err2 INT,
    @err3 INT,
    @err4 INT,
    @err5 INT,
    @err6 INT,
    @err7 INT,
    @Indexing INT,               --授课课程主键
    @TeachClassID VARCHAR(36),           --虚拟班主键
    @StudentID VARCHAR(36)               --学生主键

BEGIN

    --选择要删除授课课程记录的主键
    SELECT @Indexing=Indexing FROM  <SPAN style="COLOR: #3333ff"><STRONG>deleted  --删除临时表</STRONG>
</SPAN>
    --开启事务
    BEGIN TRANSACTION

        /*删除授课课程记录、授课课程与虚拟班关系、虚拟班
        字段为 “否”*/

        --彻底删除授课课程记录
        DELETE TBR_TeachCourseLink  WHERE Indexing=@Indexing AND IsAvailable='否'
        SET @err1=@@ERROR

        /*删除授课课程与选修课学生关系、学生表
        字段为 “否”*/
        --删除授课课程与选修课学生的关系表
        DELETE TBR_StuChooseCourseLink   WHERE Indexing=@Indexing and IsAvailable='否'
        SET @err6=@@ERROR

        --删除学生表(需要触发多条记录，用游标操作)
        --声明游标
        DECLARE StudentCursor CURSOR FOR
        SELECT StudentID FROM TBR_StuChooseCourseLink  WHERE Indexing=@Indexing
        --打开游标
            OPEN StudentCursor
                    FETCH NEXT FROM StudentCursor INTO @StudentID  --给变量赋初始值
                    WHILE @@FETCH_STATUS=0 --语句执行成功
                    begin
                    --更新中的记录
                    DELETE TB_Student WHERE StudentID=@StudentID and  isAvailable = '否'
                    FETCH NEXT FROM StudentCursor INTO @StudentID
                    end
            --关闭并释放游标
            CLOSE StudentCursor
        DEALLOCATE StudentCursor
        SET @err7=@@ERROR

    --判断是否执行成功
    IF (@err1 =0 and @err2=0 and @err3=0 and @err4=0 and @err5=0 and @err6=0 and @err7=0)
        --提交事务
        COMMIT TRANSACTION
    ELSE
        --事务回滚
        ROLLBACK TRANSACTION


END</SPAN>

           触发器的工作原理以及什么时候使用两张临时表进行操作，可以用下面这张图表示：Inserted和Deleted两个临时表实现。