很多人應(yīng)該都用過存儲過程和觸發(fā)器，而觸發(fā)器并不是一個新的概念,它只是一個特殊一些的存儲過程, 它可以根據(jù)執(zhí)行的TQL 語句自動執(zhí)行,不需要我們?nèi)フ{(diào)用。
觸發(fā)器是為了數(shù)據(jù)庫表結(jié)構(gòu)的完成性、一致性而使用的，和單個表的約束一樣,觸發(fā)器是對于表之間的約束,它比主鍵約束范圍更廣泛一些,作用范圍更大一些,下面給大家介紹一下常用的三種觸發(fā)器.
【插入觸發(fā)器(Insert Trigger)】
當(dāng)向表里面插入記錄時候觸發(fā)
通過臨時表（Inserted）來實現(xiàn)，我們需要往表里插入一條記錄時，插入的記錄先插入到臨時表中，然后，從臨時表插入到目標(biāo)表中，為什么采用臨時表，我覺的是因為觸發(fā)器是自動觸發(fā)，既然是自動觸發(fā)就不會往里面?zhèn)鲄?shù)，那么參數(shù)怎么傳入到目標(biāo)表中呢，為了傳入?yún)?shù)加入了臨時表，將要插入或者刪除的記錄先放到了臨時表中，再從臨時表插入數(shù)據(jù)。
下面是一個校區(qū)的基本表TB_Campus，向表里插入數(shù)據(jù)時觸發(fā)該觸發(fā)器，自動向該小區(qū)下的建筑表TB_Building添加數(shù)據(jù)記錄，從而保證這兩個表的數(shù)據(jù)一致性。
觸發(fā)器通常是為了兩個表或多個表中數(shù)據(jù)一致加入的。
例如：
- <SPAN style="FONT-SIZE: 18px">
-
-
-
-
- CREATE TRIGGER [dbo].[TR_Campus_Add]
- on [dbo].[TB_Campus]
-
- INSTEAD OF INSERT
-
- AS
- DECLARE
- @BuildingID VARCHAR(50),
- @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>
-- =============================================
-- Author: 評教小組-李龍生
-- Create date: 2012年12月29日15:02:06
-- Description: 添加校區(qū)記錄觸發(fā)器
-- =============================================
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
--獲取要插入記錄的標(biāo)記號（建筑）
SELECT @BuildingID=BuildingID from inserted --插入臨時表
--插入基本表中的信息（建筑表）
INSERT INTO TB_Building(BuildingID,BuildingCode,BuildingName,Descriptions,Operator)
VALUES(@BuildingID，@BuildingCode,@BuildingName,@Descriptions,@Operator)
END
【更新觸發(fā)器(Update Trigger)】
當(dāng)更新表中的任意一個字段時觸發(fā)
更新觸發(fā)器需要用到兩個表，一個是Inserted 表，另一個是Deleted表，Inserted表存儲更新后的記錄，Deleted表存儲更新后的記錄，更新一個表示需要先將要刪除的記錄放入Deleted表中，然后，將Inserted表的新記錄插入表中，從而實現(xiàn)更新操作。
例如：下面是教務(wù)系統(tǒng)中一個觸發(fā)器，更新老師課程關(guān)系表（TR_TeachCourseLink）記錄，并更新與這個表有關(guān)的表。
在更新、刪除觸發(fā)器中我們需要注意一個問題，觸發(fā)器為什么會觸發(fā)呢，它是基于SQL 語句而執(zhí)行的，但更新、刪除語句可以一次性刪除多條記錄，其實SQL 語句只執(zhí)行了一次，即觸發(fā)器也只執(zhí)行一次，那么當(dāng)涉及到表關(guān)系為一對多或者多對多時，就會出現(xiàn)更新或刪除不完下面表中記錄
為了解決這一問題，我們在下面存儲過程中加入了游標(biāo)，使得更新或刪除記錄時一條一條的刪除，每刪除一條，觸發(fā)器就會執(zhí)行一次，使得數(shù)據(jù)保持一致，不過，這樣可能大大降低了SQL語句執(zhí)行速度，如果數(shù)據(jù)量很大，速度會變慢。
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: 假刪除授課表中課表記錄
-- 授課課程與虛擬班關(guān)系、虛擬班
-- 授課課程與上課班學(xué)生關(guān)系、上課班學(xué)生
-- 課程與選修課學(xué)生關(guān)系、選修課學(xué)生
-- =============================================
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) --學(xué)生主鍵
BEGIN
--選擇要刪除授課課程記錄的主鍵
SELECT @Indexing=Indexing FROM deleted --刪除臨時表
--開啟事務(wù)
BEGIN TRANSACTION
/*更新授課課程記錄、授課課程與虛擬班關(guān)系、虛擬班，各表中的IsAvailable
字段為 “否”*/
--更新授課課程記錄 IsAvailable='否'
UPDATE TBR_TeachCourseLink SET IsAvailable='否' WHERE Indexing=@Indexing
SET @err1=@@ERROR
--更新虛擬班與TeachCourse表的關(guān)系 IsAvailable='否'
UPDATE TBR_CourseTeachClassLink SET IsAvailable='否' WHERE Indexing=@Indexing
SET @err2=@@ERROR
--更新虛擬班表 IsAvailable='否'(需要觸發(fā)多條記錄，用游標(biāo)操作)
--聲明游標(biāo)
DECLARE TeachClassCursor CURSOR FOR
SELECT TeachClassID FROM TBR_CourseTeachClassLink WHERE Indexing=@Indexing
--打開游標(biāo)
OPEN TeachClassCursor
FETCH NEXT FROM TeachClassCursor INTO @TeachClassID --給變量賦初始值
WHILE @@FETCH_STATUS=0 --語句執(zhí)行成功
BEGIN
--更新中的記錄
Update TB_TeachClass SET isAvailable = '否' WHERE TeachClassID=@TeachClassID
FETCH NEXT FROM TeachClassCursor INTO @TeachClassID
END
--關(guān)閉并釋放游標(biāo)
CLOSE TeachClassCursor
DEALLOCATE TeachClassCursor
SET @err3=@@ERROR
/*更新授課課程與選修課學(xué)生關(guān)系、學(xué)生表，各表中的IsAvailable
字段為 “否”*/
--更新授課課程與選修課學(xué)生的關(guān)系表IsAvailable='否'
UPDATE TBR_StuChooseCourseLink SET IsAvailable='否' WHERE Indexing=@Indexing
SET @err6=@@ERROR
--更新學(xué)生表，將IsAvailable字段變?yōu)?否'(需要觸發(fā)多條記錄，用游標(biāo)操作)
--聲明游標(biāo)
DECLARE StudentCursor CURSOR FOR
SELECT StudentID FROM TBR_StuChooseCourseLink WHERE Indexing=@Indexing
--打開游標(biāo)
OPEN StudentCursor
FETCH NEXT FROM StudentCursor INTO @StudentID --給變量賦初始值
WHILE @@FETCH_STATUS=0 --語句執(zhí)行成功
begin
--更新中的記錄
Update TB_Student SET isAvailable = '否' WHERE StudentID=@StudentID
FETCH NEXT FROM StudentCursor INTO @StudentID
end
--關(guān)閉并釋放游標(biāo)
CLOSE StudentCursor
DEALLOCATE StudentCursor
SET @err7=@@ERROR
--判斷是否執(zhí)行成功
IF (@err1 =0 and @err2=0 and @err3=0 and @err4=0 and @err5=0 and @err6=0 and @err7=0)
--提交事務(wù)
COMMIT TRANSACTION
ELSE
--事務(wù)回滾
ROLLBACK TRANSACTION
END
【刪除觸發(fā)器(Delete Trigger)】
當(dāng)刪除表中記錄時觸發(fā)
先把要刪除的記錄放到Deleted臨時表中，然后，再刪除。
例如：
- <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
-
-
-
-
-
-
- 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
- </SPAN>
-
- BEGIN TRANSACTION
-
- /*刪除授課課程記錄、授課課程與虛擬班關(guān)系、虛擬班
- 字段為 “否”*/
-
-
- DELETE TBR_TeachCourseLink WHERE Indexing=@Indexing AND IsAvailable='否'
- SET @err1=@@ERROR
-
- /*刪除授課課程與選修課學(xué)生關(guān)系、學(xué)生表
- 字段為 “否”*/
-
- 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>
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: 徹底刪除授課表中課表記錄
-- 課程與選修課學(xué)生關(guān)系、選修課學(xué)生
-- =============================================
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) --學(xué)生主鍵
BEGIN
--選擇要刪除授課課程記錄的主鍵
SELECT @Indexing=Indexing FROM deleted --刪除臨時表
--開啟事務(wù)
BEGIN TRANSACTION
/*刪除授課課程記錄、授課課程與虛擬班關(guān)系、虛擬班
字段為 “否”*/
--徹底刪除授課課程記錄
DELETE TBR_TeachCourseLink WHERE Indexing=@Indexing AND IsAvailable='否'
SET @err1=@@ERROR
/*刪除授課課程與選修課學(xué)生關(guān)系、學(xué)生表
字段為 “否”*/
--刪除授課課程與選修課學(xué)生的關(guān)系表
DELETE TBR_StuChooseCourseLink WHERE Indexing=@Indexing and IsAvailable='否'
SET @err6=@@ERROR
--刪除學(xué)生表(需要觸發(fā)多條記錄，用游標(biāo)操作)
--聲明游標(biāo)
DECLARE StudentCursor CURSOR FOR
SELECT StudentID FROM TBR_StuChooseCourseLink WHERE Indexing=@Indexing
--打開游標(biāo)
OPEN StudentCursor
FETCH NEXT FROM StudentCursor INTO @StudentID --給變量賦初始值
WHILE @@FETCH_STATUS=0 --語句執(zhí)行成功
begin
--更新中的記錄
DELETE TB_Student WHERE StudentID=@StudentID and isAvailable = '否'
FETCH NEXT FROM StudentCursor INTO @StudentID
end
--關(guān)閉并釋放游標(biāo)
CLOSE StudentCursor
DEALLOCATE StudentCursor
SET @err7=@@ERROR
--判斷是否執(zhí)行成功
IF (@err1 =0 and @err2=0 and @err3=0 and @err4=0 and @err5=0 and @err6=0 and @err7=0)
--提交事務(wù)
COMMIT TRANSACTION
ELSE
--事務(wù)回滾
ROLLBACK TRANSACTION
END
觸發(fā)器的工作原理以及什么時候使用兩張臨時表進行操作，可以用下面這張圖表示：Inserted和Deleted兩個臨時表實現(xiàn)。
