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總結(六)觸發器
相關文章
- sql-server觸發器SQLServer觸發器
- SQL Server:觸發器詳解SQLServer觸發器
- SQL觸發器例項講解SQL觸發器
- SQL Server 觸發器詳情HOPPSQLServer觸發器
- 瞭解SQL Server觸發器及觸發器中的事務AWSQLServer觸發器
- 淺入淺出SQL Server 觸發器SQLServer觸發器
- SAP工作流觸發總結
- MySQL與SQL的觸發器的不同寫法MySql觸發器
- web移動開發總結(六)Web移動開發
- 總結Minor GC、Full GC觸發條件GC
- 暫存器,觸發器,三極體小結觸發器
- SQL Server實戰五:儲存過程與觸發器SQLServer儲存過程觸發器
- 第六週總結
- mysql觸發器MySql觸發器
- D觸發器觸發器
- pt-osc使用增量資料觸發器的不足之處總結和推薦觸發器
- SQL查詢總結SQL
- 第六週總結(2024.8.10)
- SqlServer-觸發器SQLServer觸發器
- logon觸發器for dbaGo觸發器
- MySQL使用觸發器MySql觸發器
- 語句觸發器觸發器
- MySql-觸發器MySql觸發器
- sqlserver 列觸發器SQLServer觸發器
- 除錯觸發器除錯觸發器
- 建立MySQL觸發器MySql觸發器
- sql注入簡單總結SQL
- mybatis動態sql總結MyBatisSQL
- SQL進階總結(二)SQL
- 手動執行SQL觸發器id自增報錯處理方式SQL觸發器
- iOS面試題總結(六)iOS面試題
- CTF之做題總結(六)
- 2024暑假第六週總結
- 觸發器 REFERENCING OLD AS OLD觸發器
- 行為和觸發器觸發器
- MySQL觸發器介紹MySql觸發器
- SQL語句規範總結SQL
- sql語句學習總結SQL
- SQL隱碼攻擊總結SQL