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

电玩女神 2022-04-13 10:21 255阅读 0赞

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

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

**【插入触发器(Insert Trigger)】**

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

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

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

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

例如：

-- ============================================= -- 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 inserted --插入临时表 --插入基本表中的信息（建筑表） INSERT INTO TB_Building(BuildingID,BuildingCode,BuildingName,Descriptions,Operator) VALUES(@BuildingID，@BuildingCode,@BuildingName,@Descriptions,@Operator) END

**【更新触发器(Update Trigger)】**

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

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

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

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

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

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 deleted --删除临时表 --开启事务 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='否'(需要触发多条记录，用游标操作) --声明游标 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 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

**【删除触发器(Delete Trigger)】**

当删除表中记录时触发

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

例如：

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 deleted --删除临时表 --开启事务 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

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

![1357132867_7593.png][]

[1357132867_7593.png]: https://img-my.csdn.net/uploads/201301/02/1357132867_7593.png