SQL總結（六）觸發器

概念

觸發器是一種特殊型別的儲存過程，不由使用者直接呼叫。建立觸發器時會對其進行定義，以便在對特定表或列作特定型別的資料修改時執行。

觸發器可以查詢其他表，而且可以包含複雜的 SQL 語句。 它們主要用於強制服從複雜的業務規則或要求。 例如，您可以根據客戶當前的帳戶狀態，控制是否允許插入新訂單。
觸發器也可用於強制引用完整性，以便在多個表中新增、更新或刪除行時，保留在這些表之間所定義的關係。

作用

1）觸發器可通過資料庫中的相關表實現級聯更改；通過級聯引用完整性約束可以更有效地執行這些更改。

2）觸發器可以強制比用 CHECK 約束定義的約束更為複雜的約束。與 CHECK 約束不同，觸發器可以引用其它表中的列。例如，觸發器可以使用另一個表中的 SELECT 比較插入或更新的資料，以及執行其它操作，如修改資料或顯示使用者定義錯誤資訊。

3）觸發器還可以強制執行業務規則

4）觸發器也可以評估資料修改前後的表狀態，並根據其差異采取對策。

實際應用

儘管觸發器有很多優點，但是在實際的專案開發中，特別是OOP思想的深入，觸發器的弊端也逐漸突顯，主要：

1、過多的觸發器使得資料邏輯變得複雜

2、資料操作比較隱含，不易進行調整修改

3、觸發器的功能逐漸在程式碼邏輯或事務中替代實現，更符合OO思想。

建議：

使用觸發器需慎重。

語法

CREATE TRIGGER trigger_name 
ON {table_name | view_name} 
{FOR | After | Instead of } [ insert, update,delete ]
AS           
    sql_statement  

觸發器型別

SQL Server 包括兩種常規型別的觸發器：資料操作語言 (DML) 觸發器和資料定義語言 (DDL) 觸發器。 當INSERT、UPDATE 或 DELETE 語句修改指定表或檢視中的資料時，可以使用 DML 觸發器。 DDL 觸發器激發儲存過程以響應各種 DDL 語句，這些語句主要以CREATE、ALTER 和 DROP 開頭。 DDL 觸發器可用於管理任務，例如稽核和控制資料庫操作。

通常說的觸發器就是DML觸發器。

DML 觸發器在 INSERT、UPDATE 和 DELETE 語句上操作，並且有助於在表或檢視中修改資料時強制業務規則，擴充套件資料完整性。

在SQL Server2005後又增加了DDL觸發器。

DDL 觸發器將激發儲存過程以響應事件。但與 DML 觸發器不同的是，它們不會為響應針對表或檢視的 UPDATE、INSERT 或 DELETE 語句而激發。相反，它們將為了響應各種資料定義語言 (DDL) 事件而激發。這些事件主要與以關鍵字 CREATE、ALTER 和 DROP 開頭的 Transact-SQL 語句對應。執行 DDL 式操作的系統儲存過程也可以激發 DDL 觸發器。

DDL 觸發器使用場合：

• 要防止對資料庫架構進行某些更改。
• 希望資料庫中發生某種情況以響應資料庫架構中的更改。
• 要記錄資料庫架構中的更改或事件。

在這裡我們只講述DML觸發器。DML觸發器又分以下分類：

1、 After觸發器

After觸發器要求只有執行某一操作insert、update、delete之後觸發器才被觸發，且只能定義在表上。

• 1）insert觸發器
• 2）update觸發器
• 3）delete觸發器

2、Instead of 觸發器

Instead of 觸發器表示並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身。既可以在表上定義instead of觸發器，也可以在檢視上定義。

inserted與deleted對比

觸發器有兩個特殊的表：插入表（instered表）和刪除表（deleted表）。這兩張是邏輯表也是虛表。有系統在記憶體中建立者兩張表，不會儲存在資料庫中。而且兩張表的都是隻讀的，只能讀取資料而不能修改資料。這兩張表的結果總是與被改觸發器應用的表的結構相同。當觸發器完成工作後，這兩張表就會被刪除。Inserted表的資料是插入或是修改後的資料，而deleted表的資料是更新前的或是刪除的資料。

對錶的操作	Inserted邏輯表	Deleted邏輯表
增加記錄（insert）	存放增加的記錄	無
刪除記錄（delete）	無	存放被刪除的記錄
修改記錄（update）	存放更新後的記錄	存放更新前的記錄

在觸發器實際應用中，主要還是建立約束以及級聯更新。在這裡主要通過簡單例項予以說明。

1、觸發器新增

原理：

當觸發INSERT觸發器時，新的資料行就會被插入到觸發器表和inserted表中。inserted表是一個邏輯表，它包含了已經插入的資料行的一個副本。inserted表包含了INSERT語句中已記錄的插入動作。inserted表還允許引用由初始化INSERT語句而產生的日誌資料。觸發器通過檢查inserted表來確定是否執行觸發器動作或如何執行它。inserted表中的行總是觸發器表中一行或多行的副本。

場景：增加學生資訊時，要校驗其年齡，暫定其年齡必須大於18，否則新增失敗

作用：校驗約束

具體例項：

--觸發器新增：只允許錄取18歲以上學生
IF OBJECT_ID (N'TRIGER_Students_Insert', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Students_Insert;
GO
CREATE TRIGGER TRIGER_Students_Insert
ON Students
FOR INSERT
AS
    declare @age int
    select @age=COUNT(Students.ID) FROM Students INNER JOIN inserted ON Students.ID =inserted.ID    
    PRINT @age
    if(@age<18)
    begin
        raiserror('學生年齡必須要大於18哦',16,8)
        rollback tran
    end

執行insert:

INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(105,'李四',16,'BeiJing',11)

執行結果：

會直接異常，返回錯誤資訊

訊息 50000，級別 16，狀態 8，過程 TRIGER_Students_Insert，第 10 行
學生年齡必須要大於18哦
訊息 3609，級別 16，狀態 1，第 1 行
事務在觸發器中結束。批處理已中止。

2、觸發器更新

原理：

可將UPDATE語句看成兩步操作：即捕獲資料前像(before image)的DELETE語句，和捕獲資料後像(after image)的INSERT語句。當在定義有觸發器的表上執行UPDATE語句時，原始行（前像）被移入到deleted表，更新行（後像）被移入到inserted表。

觸發器檢查deleted表和inserted表以及被更新的表，來確定是否更新了多行以及如何執行觸發器動作。

可以使用IF UPDATE語句定義一個監視指定列的資料更新的觸發器。這樣，就可以讓觸發器容易的隔離出特定列的活動。當它檢測到指定列已經更新時，觸發器就會進一步執行適當的動作，例如發出錯誤資訊指出該列不能更新，或者根據新的更新的列值執行一系列的動作語句。

場景：

專業資訊ID修改，對應的學生資訊中專業ID也相應進行修改

例項實現：

--更新觸發器：更新專業ID時,同時更新學生的專業資訊
IF OBJECT_ID (N'TRIGER_Majors_Update', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Majors_Update;
GO
CREATE TRIGGER TRIGER_Majors_Update
ON Majors
FOR UPDATE
AS
    IF UPDATE(ID)
    UPDATE Students Set MajorID=inserted.ID
    FROM Students,deleted,inserted
    WHERE Students.MajorID = deleted.ID

原始資料：

執行更新操作：

UPDATE Majors SET ID=12 WHERE ID=11

執行結果：

3、觸發器刪除

原理：

當觸發DELETE觸發器後，從受影響的表中刪除的行將被放置到一個特殊的deleted表中。deleted表是一個邏輯表，它保留已被刪除資料行的一個副本。deleted表還允許引用由初始化DELETE語句產生的日誌資料。

使用DELETE觸發器時，需要考慮以下的事項和原則：

• 當某行被新增到deleted表中時，它就不再存在於資料庫表中；因此，deleted表和資料庫表沒有相同的行。
• 建立deleted表時，空間是從記憶體中分配的。deleted表總是被儲存在快取記憶體中。
• 為DELETE動作定義的觸發器並不執行TRUNCATE TABLE語句，原因在於日誌不記錄TRUNCATE TABLE語句。

場景：學校某選修課取消。

處理邏輯：在刪除課程的同時，需要刪除該課程的選課資訊。

觸發器：

--刪除觸發器：刪除課程時,同時刪除該課程的選課資訊
IF OBJECT_ID (N'TRIGER_Courses_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Delete
ON Courses
FOR DELETE
AS
    DELETE SC
    FROM SC,deleted     
    WHERE SC.CourseID = deleted.ID

原始資料：

執行課程刪除操作：

DELETE FROM Courses WHERE ID=10

執行結果：

可以看到，刪除課程的同時，選修課程10的選課記錄也被刪除。

4、Instead Of 觸發器

用Instead Of觸發器實現與例項3相同的功能，具體實現程式碼如下：

--Instead Of觸發器：刪除課程時,同時刪除該課程的選課資訊
IF OBJECT_ID (N'TRIGER_Courses_Instead_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Instead_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Instead_Delete
ON Courses
Instead Of DELETE
AS
    declare @courseId int
    --獲取要刪除的課程ID
    SELECT @courseId=ID FROM deleted
    --刪除選課資訊
    DELETE FROM SC WHERE CourseID = @courseId
    --刪除課程資訊
    DELETE FROM Courses WHERE ID=@courseId

執行刪除：

--測試用例
DELETE FROM Courses WHERE ID=10

測試結果：

其測試結果與例項3相同。

本文測試用例指令碼：

--資料準備
--學生資訊表
IF OBJECT_ID (N'Students', N'U') IS NOT NULL
    DROP TABLE Students;
GO
CREATE TABLE Students(
    ID int primary key not null,
    Name nvarchar(50),
    Age int,
    City nvarchar(50),
    MajorID int
)


--專業資訊表
IF OBJECT_ID (N'Majors', N'U') IS NOT NULL
    DROP TABLE Majors;
GO
CREATE TABLE Majors(
    ID int primary key not null,
    Name nvarchar(50)
)

--課程表
IF OBJECT_ID (N'Courses', N'U') IS NOT NULL
    DROP TABLE Courses;
GO
CREATE TABLE Courses(
    ID int primary key not null,
    Name nvarchar(50) not null
)

IF OBJECT_ID (N'SC', N'U') IS NOT NULL
    DROP TABLE SC;
GO
--選課表
CREATE TABLE SC(
    StudentID int not null,
    CourseID int not null,
    Score int    
)

/*
基礎資料
*/

--專業資訊
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'法律')
INSERT INTO Majors(ID,Name) VALUES(11,'美學')

--課程資訊
DELETE FROM Courses
INSERT INTO Courses(ID,Name) VALUES (10,'太極拳')
INSERT INTO Courses(ID,Name) VALUES (11,'攝影入門')
INSERT INTO Courses(ID,Name) VALUES (12,'生命科學導論')

--學生資訊
DELETE FROM Students
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(103,'李明',20,'BeiJing',11)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(104,'王濤',18,'ShangHai',11)


--選課資訊
DELETE FROM SC
INSERT INTO SC(StudentID,CourseID) VALUES(101,10)
INSERT INTO SC(StudentID,CourseID) VALUES(101,11)
INSERT INTO SC(StudentID,CourseID) VALUES(102,12)



--觸發器新增：只允許錄取18歲以上學生
IF OBJECT_ID (N'TRIGER_Students_Insert', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Students_Insert;
GO
CREATE TRIGGER TRIGER_Students_Insert
ON Students
FOR INSERT
AS
    declare @age int
    select @age=COUNT(Students.ID) FROM Students INNER JOIN inserted ON Students.ID =inserted.ID    
    PRINT @age
    if(@age<18)
    begin
        raiserror('學生年齡必須要大於18哦',16,8)
        rollback tran
    end

--測試用例
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(105,'李四',16,'BeiJing',11)
SELECT * FROM Students

--更新觸發器：更新專業ID時,同時更新學生的專業資訊
IF OBJECT_ID (N'TRIGER_Majors_Update', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Majors_Update;
GO
CREATE TRIGGER TRIGER_Majors_Update
ON Majors
FOR UPDATE
AS
    IF UPDATE(ID)
    UPDATE Students Set MajorID=inserted.ID
    FROM Students,deleted,inserted
    WHERE Students.MajorID = deleted.ID

--測試用例
UPDATE Majors SET ID=12 WHERE ID=11

SELECT * FROM Students
SELECT * FROM Majors

--刪除觸發器：刪除課程時,同時刪除該課程的選課資訊
IF OBJECT_ID (N'TRIGER_Courses_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Delete
ON Courses
FOR DELETE
AS
    DELETE SC
    FROM SC,deleted     
    WHERE SC.CourseID = deleted.ID

--測試用例
DELETE FROM Courses WHERE ID=10

--執行結果
SELECT * FROM Students
SELECT * FROM Courses
SELECT * FROM SC



--Instead Of觸發器：刪除課程時,同時刪除該課程的選課資訊
IF OBJECT_ID (N'TRIGER_Courses_Instead_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Instead_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Instead_Delete
ON Courses
Instead Of DELETE
AS
    declare @courseId int
    --獲取要刪除的課程ID
    SELECT @courseId=ID FROM deleted
    --刪除選課資訊
    DELETE FROM SC WHERE CourseID = @courseId
    --刪除課程資訊
    DELETE FROM Courses WHERE ID=@courseId

--測試用例
DELETE FROM Courses WHERE ID=10

--執行結果
SELECT * FROM Students
SELECT * FROM Courses
SELECT * FROM SC

其他

關於raiserror可見：

https://msdn.microsoft.com/zh-cn/library/ms178592.aspx

轉載自：SQL總結（六）觸發器