《SQL Server 2008從入門到精通》--20180717

来源:https://www.cnblogs.com/kukubear0/archive/2018/07/19/9337116.html
-Advertisement-
Play Games

觸發器，DML觸發器（DELETE觸發器，INSERT觸發器，UPDATE觸發器，INSTEAD OF觸發器），DDL觸發器，嵌套觸發器，遞歸觸發器，啟用禁用觸發器，資料庫級別觸發器，伺服器級別觸發器 ...


目錄

1.觸發器

觸發器是一種特殊的存儲過程，與表緊密關聯。

1.1.DDL觸發器

當伺服器或資料庫中發生數據定義語言（DDL）事件時將被調用。如CREATE,ALTER，DROP等操作。如果要執行以下操作，可以使用DDL觸發器：

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

1.2.DML觸發器

當資料庫伺服器中發生數據操作語言（DML）事件時將被調用。如INSERT,DELETE,UPDATE等操作。將DML觸發器和觸發語句作為可在觸發器內回滾的單個事務對待，如果檢測到錯誤，則整個事務回滾。DML觸發器在一下方面非常有用：

  • 可實現資料庫相關表之間的級聯更改
  • 可以防止惡意或錯誤的DML語句事件，並強制執行比CHECK約束更為複雜的其他限制
  • 可以評估數據修改前後表的狀態，並根據該差異採取措施

一個表中的多個同類DML觸發器，允許用多個不同的操作來響應同一個修改語句
SQL Server 2008為每個觸發器創建了2個特殊的表：INSERTED表和DELETED表。這是兩個邏輯表，由系統來創建和維護，用戶不能對他們進行修改。它們存放在記憶體中，而不是在資料庫中，並且結構與被DML觸發器作用的表的結構相同。
INSERTED表中存放了由執行INSERT或UPDATE語句而插入的所有行，在執行INSERT或UPDATE語句時，新的行將同時被插入到觸發器作用的表和INSERTED表中。INSERTED表中的行是觸發器作用的表中行的副本。
DELETED表中存放了由執行DELETE或UPDATE語句而刪除的所有行，在執行DELETE或UPDATE語句時，被刪除的行將由觸發器作用的表中被移動到DELETED表，兩個表中不會有重覆行。

1.3.創建觸發器

1.3.1.創建DML觸發器

1.3.1.1.INSERT觸發器

示例1：創建一個觸發器Automatic_division，當在Student表中插入一條學生信息時，觸發器根據入學分數（stu_enter_score）對學生進行自動分班，併在class_student表中插入一條記錄。
分班要求：
|Stu_enter_score |Class_id |Class_name|
|-------------------|------------------|--------------|
|stu_enter_score>=700| 01| 創新A班|
|650<=Stu_enter_score<700| 02| 重點B班|
|600<=Stu_enter_score<650| 03| 提高C班|
|550<=Stu_enter_score<600| 04| 普通D班|
|500<=Stu_enter_score<550| 05| 普通E班|
|Stu_enter_score<500| 06| 普通F班|
執行下列語句

CREATE TRIGGER automatic_division
ON student--新建一個檢測student表的觸發器,命名automatic_division
FOR INSERT--檢測到INSERT操作時觸發器工作
AS
DECLARE @score INT,@stu_no VARCHAR(8),@class_id CHAR(2)
--聲明三個變數
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY--聲明一個指向inserted表的局部游標stu_cursor
FOR SELECT stu_no,stu_enter_score FROM inserted
OPEN stu_cursor--打開游標
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--將游標指向inserted表的第一個數據並把游標指向的stu_no和stu_enter_score值分別賦值給@stu_no和@score
WHILE @@FETCH_STATUS=0--開始迴圈
BEGIN
BEGIN--先對@score的數值範圍做判斷，以確定該學生的班級編號
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
--判斷結束
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--將數據插入到class_student表中
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--將游標移向inserted表的下一個數據，重覆這個迴圈
END--迴圈結束
CLOSE stu_cursor--關閉游標
DEALLOCATE stu_cursor--釋放游標資源
GO

驗證代碼是否正確
向student表中插入數據，並查看class_student表中的數據是否正確

INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180001','鄒莉莉','女','389'),
('20180002','萬興','男','701'),
('20180003','孫偉','男','652'),
('20180004','溫佳靜','女','676'),
('20180005','薑立夫','男','542')

Class_student表中的數據如圖所示

游標示例2：對student表中還未分班的學生進行分班
Student表中的數據如圖所示

其中stu_no為20180001~20180005的學生已經在示例1中分班，剩下的學生全都未分班。
執行下列語句

ALTER TABLE student
ADD stu_division_state bit--為student表新建一列記錄是否已分班，true表示已分班
GO
DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游標stu_class_cursor指向class_student表的所有數據
OPEN stu_class_cursor--打開游標
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--關閉游標
DEALLOCATE stu_class_cursor--釋放游標資源
---所有學生是否分班已經全部記錄在stu_division_state中
GO
DECLARE @stu_no VARCHAR(8),@score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state IS NULL
--新建student表的游標stu_cursor，指向所有未分班學生
OPEN stu_cursor--打開游標
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--迴圈開始
BEGIN
BEGIN--先對@score的數值範圍做判斷，以確定該學生的班級編號
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--將數據插入到class_student表中
UPDATE student--將student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--將游標移向inserted表的下一個數據，重覆這個迴圈
END--迴圈結束
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

結果如圖所示
Student表的數據

Class_student表的數據

至此Student表中所有學生都已分班
為了以後方便，可以將游標示例2中的代碼稍作修改封裝成一個用戶自定義存儲過程
存儲過程示例3
修改後的代碼如下

CREATE PROCEDURE student_division
AS
BEGIN
UPDATE student
SET stu_division_state=0--先將student表中所有學生的分班情況都標成未分班

DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游標stu_class_cursor指向class_student表的所有數據
OPEN stu_class_cursor--打開游標
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no--利用游標找出student表中已分班的學生並標記分班狀態
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--關閉游標
DEALLOCATE stu_class_cursor--釋放游標資源
---所有學生是否分班已經全部記錄在stu_division_state中

DECLARE @score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state=0
--新建student表的游標stu_cursor，指向所有未分班學生
OPEN stu_cursor--打開游標
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--迴圈開始
BEGIN
BEGIN--先對@score的數值範圍做判斷，以確定該學生的班級編號
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--將數據插入到class_student表中
UPDATE student--將student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--將游標移向inserted表的下一個數據，重覆這個迴圈
END--迴圈結束
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO

註：和游標示例2的代碼相比，示例3的代碼添加了將所有學生分班狀態標記為0的過程，去掉了添加stu_division_state列的過程，但對原來已有的學生的分班狀態賦值這個步驟並未刪去，而是進行重覆校驗。並且刪除了兩段代碼中的GO和第二段用於給學生分班的代碼中對@stu_no變數的重覆聲明。

對student表插入數據並運行student_division的存儲過程

註：對student表插入數據前應先禁用示例1的觸發器automatic_division

執行下列語句

ALTER TABLE student DISABLE TRIGGER automatic_division
--禁用automatic_division觸發器
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score,stu_division_state)
VALUES('20180006','王洋','男','724',NULL),
('20180007','易陽','男','713',NULL),
('20180008','孫浩','男','584',NULL),
('20180009','張秋燕','女','420','False'),
('20180010','胡燕','女','527','True')

Student表的數據如圖所示，紅框內就是我剛剛插入還未分班的數據，其中20180009和20180010這兩個學生的分班狀態被我誤標成False和True

執行存儲過程

EXEC dbo.student_division

結果如圖所示
Student表的數據（分班狀態都為true了）

Class_student表的數據

1.3.1.2.DELETE觸發器

當針對目標資料庫運行DELETE語句時就會激活DELETE觸發器。用戶直接運行DELETE語句和使用DELETE觸發器又有所不同，當激活DELETE觸發器後，從受觸發器影響的表中刪除的行會被放置在一個特殊的臨時表——DELETED表中。DELETED表還允許引用由初始化DELETE語句產生的日誌數據。
當DELETE觸發器被激活時，需要考慮以下幾點

  • 當某行被添加到DELETED表中時就不存在於資料庫表，因此資料庫表和DELETED表不可能存在相同行。
  • 系統自動創建DELETED表時，空間從記憶體中分配。DELETED表被存儲在高速緩存中。
  • 為DELETE操作定義的觸發器並不執行TRUNCATE TABLE語句，原因在於日誌不記錄TRUNCATE TABLE語句。

示例4：為student表定義一個DELETE觸發器，當刪除一條學生信息時，class_student表中該學生的分班信息也會被刪除
執行下麵的語句

CREATE TRIGGER delete_student
ON student
FOR DELETE
AS
DECLARE @stu_no VARCHAR(8)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor
FETCH NEXT FROM stu_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM class_student
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

測試delete_student觸發器的正確性
Student表的數據如圖所示

Class_student表的數據如圖所示

執行下列語句

DELETE FROM student
WHERE stu_enter_score<=351
--在student表中刪除入學成績小於分的學生

從student表來看，只有入學編號為20180011和20180012的學生成績被刪除。該操作激活了delete_student觸發器
Class_student表的數據如圖所示

入學編號為20180011和20180012的學生分班信息已經從class_student表中自動刪除。

1.3.1.3.UPDATE觸發器

當針對目標資料庫運行UPDATE語句時就會激活UPDATE觸發器。對UPDATE觸發器來說，臨時表INSERTED和DELETED依然有用。UPDATE觸發器被激活時，原始行被移入DELETED表中，更新行被移入到INSERTED表中。觸發器檢查DELETED表和INSERTED表以及被更新的表，來確定是否更新了多行和如何執行觸發器動作。
Student表的數據如圖所示

Class_student表的數據如圖所示

示例5：當student表中的stu_no欄位更新時，同步更新class_student表中的stu_no欄位
執行下列語句新建觸發器update_stu_no_single

CREATE TRIGGER update_stu_no_single
ON student
FOR UPDATE
AS
IF UPDATE(stu_no)
BEGIN
UPDATE class_student
SET stu_no=(SELECT stu_no FROM inserted)
WHERE stu_no=(SELECT stu_no FROM deleted)
END
GO

驗證update_stu_no_single觸發器是否正確，在Student表中執行下列語句，將student表中stu_no為“20180101”的學生的stu_no改成00000000

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

執行成功後，update_stu_no_single觸發器被激活，class_student表的數據如圖所示

註：update_stu_no_single觸發器只能對單行記錄的UPDATE操作起效，如果批量UPDATE stu_no，執行語句時會提示子查詢返回的值不止1個。下麵的示例6將提供批量UPDATE stu_no的觸發器

示例6：實現當student表的stu_no欄位批量更新時，class_student表的stu_no也同步批量更新
首先將student表和class_student表的數據修改成原來的樣子，並且刪除update_stu_no_single觸發器
Student表的數據如圖所示

Class_student表的數據如圖所示

執行下列語句新建觸發器update_stu_no_batch

CREATE TRIGGER update_stu_no_batch
ON student
FOR UPDATE
AS
DECLARE @stu_no_insert VARCHAR(8),@stu_no_delete VARCHAR(8)
DECLARE stu_cursor_insert CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM inserted
OPEN stu_cursor_insert
DECLARE stu_cursor_delete CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE class_student
SET stu_no=@stu_no_insert
WHERE stu_no=@stu_no_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
END
CLOSE stu_cursor_insert
CLOSE stu_cursor_delete
DEALLOCATE stu_cursor_insert
DEALLOCATE stu_cursor_delete
GO

驗證update_stu_no_batch觸發器的準確性，對student表執行下列語句，實現批量修改操作

UPDATE student
SET stu_no='00000000'
WHERE stu_no LIKE '201801%'
GO

Student表的數據如圖所示

Class_student表的數據如圖所示

我們再來驗證update_stu_no_batch觸發器對更新單行stu_no數據是否有效。將student表和class_student表的數據改回原來的樣子，然後執行下列語句

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

Class_student表的數據如圖所示

註：在將表數據改成原來的樣子時，直接在編輯前200行中操作或者用T-SQL語句操作，對student表數據操作，不成功的話要考慮受鍵和約束的影響，對class_student表數據操作，不成功的話要考慮受觸發器影響。

1.3.1.4.INSTEAD OF觸發器

用INSTEAD OF觸發器可以指定執行觸發器，而不是執行觸發SQL語句，從而屏蔽原來的SQL語句，而轉向執行觸發器內部的語句。每個表或者視圖只能有1個INSTEAD OF觸發器。INSTEAD OF觸發器的特點是，能夠使作為觸發條件的SQL語句不執行。
Membership表的數據如圖所示

Call_slip表的數據如圖所示

示例7：對LibraryManagement資料庫里的membership表寫一個防刪除觸發器，尚有借書未還的讀者無法被刪除
執行下列語句創建member_delete_single觸發器

CREATE TRIGGER member_delete_single
ON membership
INSTEAD OF DELETE
AS
BEGIN
IF NOT EXISTS(SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未歸還')
DELETE FROM membership WHERE member_id=(SELECT member_id FROM deleted)
ELSE
BEGIN
SELECT '該用戶尚有圖書未還，無法刪除'
SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未歸還'
END
END
GO

驗證觸發器的正確性，執行下列語句

DELETE FROM membership
WHERE member_id='20060128'

結果如圖所示

該觸發器只針對DELETE一條數據有效
示例8：對LibraryManagement資料庫里的membership表寫一個防批量刪除觸發器，尚有借書未還的讀者無法被刪除
Membership表的數據如圖所示

Call_slip表的數據如圖所示

執行下列語句新建觸發器（將示例7中的member_delete_single觸發器先刪除）

CREATE TRIGGER member_delete_batch
ON membership
INSTEAD OF DELETE
AS
BEGIN
DECLARE member_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT member_id FROM deleted
OPEN member_cursor
DECLARE @member_id VARCHAR(8)
FETCH NEXT FROM member_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF NOT EXISTS(SELECT* FROM call_slip WHERE member_id=@member_id AND borrow_state='未歸還')
DELETE FROM membership WHERE member_id=@member_id
ELSE
PRINT '用戶'+@member_id+'無法刪除'
END
FETCH NEXT FROM member_cursor INTO @member_id
END
CLOSE member_cursor
DEALLOCATE member_cursor
END
GO

結果如圖所示

Membership表的數據如圖所示

示例9：對LibraryManagement資料庫里的call_slip表寫一個防超借觸發器，一個讀者的未還圖書最多只能有5本，超出不能再借（這裡還是針對批量處理數據創建觸發器）
Call_slip表的數據如圖所示

執行下列語句創建provent_overborrowing_batch觸發器

CREATE TRIGGER provent_overborrowing_batch
ON call_slip
INSTEAD OF INSERT
AS
BEGIN
DECLARE @member_id VARCHAR(8)
DECLARE borrow_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT member_id FROM inserted
OPEN borrow_cursor
FETCH NEXT FROM borrow_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF (SELECT COUNT(*) FROM call_slip WHERE member_id=@member_id AND borrow_state='未歸還')<5
INSERT INTO call_slip SELECT * FROM inserted WHERE member_id=@member_id
ELSE
PRINT '用戶'+@member_id+'已借閱且未還的圖書超過5本，無法再借'
END
FETCH NEXT FROM borrow_cursor INTO @member_id
END
END
GO

執行下列語句測試provent_overborrowing_batch觸發器的正確性，其中member_id為“20060128”的用戶借書未還超過5本，應該是無法再借的。

--測試數據
INSERT INTO call_slip(book_id,member_id,loan_period,borrow_state)
VALUES('20130002','20060128','30','未歸還'),
('20130001','20060128','20','未歸還'),
('20130003','20060128','30','未歸還'),
('20130004','20062919','30','未歸還'),
('20130005','20150821','45','未歸還')

結果如圖所示

Call_slip表的數據如圖所示，紅框里是新插入的數據

1.3.2.創建DDL觸發器

DDL觸發器只為了響應CREATE，DROP，ALTER事件而激活，它的作用域是整個資料庫或者伺服器，而不是作用域某張表或試圖。它可以有效控制哪位用戶可以修改資料庫結構以及如何修改。
示例10：創建一個DDL觸發器，控制上班時間（8:00-18:00）不能對LibraryManagement資料庫表和試圖結構進行新建，修改和刪除操作。
執行下列語句創建觸發器deny_DDL_table

CREATE TRIGGER deny_DDL_table
ON DATABASE
WITH ENCRYPTION
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
AS
DECLARE @eventdata XML
SET @eventdata=EVENTDATA()
IF(DATEPART(HOUR,GETDATE()) BETWEEN 8 AND 17)
BEGIN
SELECT '觸發器deny_DDL_table已禁止工作時間8:00-18:00對LibraryManagement資料庫的CREATE,ALTER,DROP操作'
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType,--事件類型
@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime,--時間觸發的時間
@eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName,--資料庫名字
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') AS ObjectName,--操作的對象名稱
@eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') AS ObjectType,--操作的對象類型
@eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText--操作命令文本
ROLLBACK---對操作進行回滾，也可以不回滾
END
GO

執行以下代碼以測試DDL觸發器deny_DDL_table的正確性

USE LibraryManagement
CREATE TABLE test(
t_id VARCHAR(2),
t_name VARCHAR(20)
)

結果如圖所示

註：EVENTDATA()可在觸發器內部使用，返回有關資料庫和伺服器事件的信息，以XML格式返回。只有直接在DDL或登錄觸發器內部引用EVENTDATA時，EVENTDATA才會返回數據。如果EVENTDATA由其他常式調用（即使這些常式由DDL或登錄觸發器進行調用），將返回 NULL。

1.3.3.嵌套觸發器

1.3.3.1.嵌套觸發器

如果一個觸發器在執行操作時引發了另一個觸發器，而這個觸發器又引發了下一個觸發器，那麼這些觸發器就是嵌套觸發器。嵌套觸發器在安裝時就被啟用，但是可以使用sp_configure存儲過程禁用和重新啟用嵌套。
DML觸發器和DDL觸發器最多可以嵌套32層，可以通過nested triggers來配置是否可以嵌套AFTER觸發器，但是不管此設置如何都可以嵌套INSTEAD OF觸發器。如果嵌套觸發器進入了無限迴圈，該觸發器將被終止，並且回滾整個事務。嵌套觸發器具有多種用途，比如保存前一個觸發器所影響的行的副本。
使用嵌套觸發器時應該註意以下幾點：

  • 預設情況下，嵌套觸發器配置選項開啟。
  • 在同一個觸發器事務中，一個觸發器不會被觸發兩次，觸發器不會調用他自己來響應觸發器中對同一個表的第二次更新
  • 由於觸發器是一個事務，一旦嵌套中任何一層的觸發器出現錯誤，將回滾整個事務。

示例11：有teacher_course表（教師所教課程表），course表（課程表）和course_selection表（學生選課表），寫一個嵌套觸發器，實現課程取消後，刪除教師所教課程表中關於該課程的記錄，而教師所教課程表中該課程的記錄被取消，導致該課程的學生選課記錄也做相應取消。
執行下列語句

--創建course表上的觸發器，刪除course表中的課程，teacher_course表中的記錄做對應刪除
CREATE TRIGGER course_delete_batch
ON course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT course_id FROM deleted
OPEN course_cursor
FETCH NEXT FROM course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE course_id=@course_id
FETCH NEXT FROM course_cursor INTO @course_id
END
GO
--創建teacher_course表上的觸發器，刪除教師課程表的記錄，學生選課表的記錄也做對應刪除
CREATE TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id為'+@course_id+'的課程依然正常開課，該課程的學生選課情況不予刪除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
END
GO

course_delete_batch和** teacher_course_delete_batch就形成了一個嵌套觸發器，下麵來驗證嵌套觸發器的正確性。
Course表中的數據如圖所示

Teacher_course表中的數據如圖所示

Course_selection**表中的數據如圖所示

以課程0013為例，執行下列語句

DELETE FROM course WHERE course_id='0013'

Course表的數據如圖所示

Teacher_course表的數據如圖所示

Course_selection表的數據如圖所示

所有關於0013課程的數據都被刪除。嵌套觸發器有效。

註：在觸發器teacher_course_delete_batch中，我額外加入了一個判斷，當teacher_course表中還有老師在教授這門課程時，所有關於這門課程的學生選課信息都不予刪除。這樣做在嵌套觸發器里是多餘的，刪除一門課程，必然會刪除teacher_course表中所有與這門課程有關的記錄，也必然刪除course_selection表中所有與這門課程有關的記錄，但是，這樣做可以保證該觸發器能夠獨立於嵌套觸發器被單獨激活。Teacher_course_delete_batch觸發器還能用於其他嵌套觸發器中，看示例12。

示例12：有teacher表（教師信息表）,teacher_course（教師所教課程表），和course_selection表（學生選課記錄表）,寫一個嵌套觸發器，實現當一個教師離職時，在刪除該教師所教課程信息，如果沒有教師教這門課程，再刪除該課程選課記錄。
其中teacher_course表的觸發器teacher_course_delete_batch已經在示例11中寫完，只需創建teacher表的teacher_delete_batch觸發器即可
執行下列代碼

CREATE TRIGGER teacher_delete_batch
ON teacher
FOR DELETE
AS
DECLARE @teacher_id CHAR(4)
DECLARE teacher_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT teacher_id FROM deleted
OPEN teacher_cursor
FETCH NEXT FROM teacher_cursor INTO @teacher_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE teacher_id=@teacher_id
FETCH NEXT FROM teacher_cursor INTO @teacher_id
END
GO

測試嵌套觸發器的正確性
Teacher表的數據如圖所示

Teacher_course表的數據如圖所示

Course_selection表的數據如圖所示

以刪除0012號教師路易為例，0012號教師教授0013號課程，且teacher_course表中並無其他教師教授0013號課程，按照邏輯要刪除teacher_course表中0012號教師的所教課程記錄和course_selection表中所有0013號課程的選課記錄。執行下列語句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的數據如圖所示

Teacher_course表的數據如圖所示

Course_selection表的數據如圖所示

測試結果正確
參照上面的數據，繼續測試另一種情況，以刪除0011號教師盧含笑為例，0011號教師教授0012號課程，在teacher_course表中還有其他教師教授該課程，因此嵌套觸發器會刪除teacher_course表中關於0011號教師教授課程記錄，但不會刪除course_selection表中關於0012號課程的選課記錄。執行下列語句

DELETE FROM teacher WHERE teacher_id='0011'
GO

結果如圖所示

Teacher表的數據如圖所示

Teacher_course表的數據如圖所示

Course_selection表的數據如圖所示

1.3.3.2.查看觸發器嵌套的層數

可以使用@@NESTLEVEL全局變數來查看當前觸發器嵌套的層數
示例13：在示例11的teacher_course_delete_batch觸發器中利用@@NESTLEVEL全局變數查看當前觸發器嵌套的層數
執行下列語句修改teacher_course_delete_batch觸發器

ALTER TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id為'+@course_id+'的課程依然正常開課，該課程的學生選課情況不予刪除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
SELECT @@NESTLEVEL AS NESTLEVEL
END
GO

測試teacher_course_delete_batch觸發器（數據就不看了，未影響觸發器原來的功能）
執行下列語句

DELETE FROM teacher_course WHERE teacher_id='0009'
--直接在teacher_course表中刪除，激活teacher_course_delete_batch觸發器

結果如圖所示

執行下列語句

DELETE FROM teacher WHERE teacher_id='0009'
--在teacher表中刪除，觸發teacher_delete_batch觸發器，進而觸發teacher_course_delete_batch觸發器

結果如圖所示

1.3.3.3.禁用和啟用嵌套觸發器

EXEC sp_configure 'nested triggers',0;
GO
--禁用嵌套觸發器
EXEC sp_configure 'nested triggers',1;
GO
--啟用嵌套觸發器

1.3.4.遞歸觸發器

1.3.4.1.遞歸觸發器

觸發器被激活，更改了表中數據，這種更改又激活了它自己，這種觸發器被稱為遞歸觸發器。資料庫創建時預設遞歸觸發器禁用。但可以使用ALTER DATABASE選項來啟用它。遞歸觸發器啟用的先決條件是嵌套觸發器必須是啟用狀態，如果嵌套觸發器禁用，不管遞歸觸發器的配置是什麼都將被禁用。而在遞歸觸發器中，inserted表和deleted表都只包含被上一次觸發器影響的行數據。
遞歸觸發器有以下兩種不同類型（這邊沒有合適的應用示例可舉，先不舉例了）

1.3.4.2.直接遞歸

直接遞歸觸發器是指整個遞歸過程只有它本身一個觸發器的參與。自己激活了自己。

1.3.4.3.間接遞歸

間接遞歸觸發器是指整個遞歸過程有多個觸發器參與，例如A激活B，B激活C，C激活A。可以看成是遞歸和嵌套的結合。
使用遞歸觸發器時需要註意以下幾點：
遞歸觸發器很複雜，需要經過有條理的設計和全面測試
在任意點的數據修改都會激活遞歸觸發器。只能按觸發器被激活的特定順序更新表。
所有觸發器一起構成一個大事務，任意觸發器的任意位置上的ROLLBACK語句都將取消所有數據的輸入，所有數據均被擦除。
觸發器最多只能遞歸16層，一旦有第17個觸發器參與進來，結果與ROLLBACK命令一樣，所有數據都將被擦除

1.3.4.4.啟用遞歸觸發器

可以使用SQL Server 2008的管理器工具來啟用遞歸觸發器。

1.4.管理觸發器

禁用和啟用觸發器
執行下列語句禁用和啟用觸發器

ALTER TABLE student DISABLE TRIGGER update_stu_no_single
--禁用update_stu_no_single觸發器
GO
ALTER TABLE student ENABLE TRIGGER update_stu_no_single
--啟用update_stu_no_single觸發器
GO

執行下列語句禁用和啟用資料庫級別觸發器

DISABLE TRIGGER deny_DDL_table ON DATABASE
--禁用資料庫級別觸發器deny_DDL_table
GO
ENABLE TRIGGER deny_DDL_table ON DATABASE
--啟用資料庫級別觸發器deny_DDL_table
GO

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • matplotlib的中文配置
    matplotlib的中文配置 1.matplotlib添加中文的配置 workon py3_spider 2.卸載matplotlib pip3 uninstall matplotlib 3.重新安裝 pip3 install matplotlib 4.列印python虛擬環境的matplotli ...
  • Linux常用軟體啟動、停止、重啟命令
    一、PHP 啟動命令： 停止命令： 二、MySQL 啟動命令： 停止命令： 重啟命令： 三、Apache 啟動命令： 停止命令： 重啟命令： 四、Nginx 啟動命令： 停止命令： 重啟命令： 五、MemCached 啟動命令： 停止命令： 六、Redis 啟動命令： 停止命令： 七、MongoDB ...
  • Linux中Kibana部署
    1.下載kibana安裝包kibana-5.5.0-linux-x86_64.tar.gz tar –xzf kibana-5.5.0-linux-x86_64.tar.gz解壓 把文件移動到 Mv kibana-5.5.0-linux-x86_64 /usr/local/kibana 2.配置co ...
  • Linux中kafka部署和集群
    1.下載kafka安裝包kafka_2.12-1.1.0。 tar -xzvf kafka_2.11-0.8.2.1.tgz #解壓 mv kafka_2.11-0.8.2.1 /usr/local/kafka #移動到安裝目錄 2、配置kafka mkdir /usr/local/kafka/lo ...
  • Linux中Zookeeper部署和集群部署
    自己網上下載安裝包，我下載的是tar.gz安裝包直接解壓，也可以下載rpm格式 1.下載zookeeper安裝包，放到/usr/local/zookeeper安裝包網上下載 2.解壓文件tar -zxvf zookeeper-3.4.6.tar.gz 3. 進入zookeeper-3.4.6目錄，創 ...
  • 感謝博客園
    希望這是一個新的開始吧，我將在這之後的日子記錄自己的學習以及工作成果。 2018-07-19 21:54:08 ...
  • Windows 10中Oracle資料庫導出到Access資料庫（MDB）
    本篇博文簡單介紹將Oracle資料庫中表導出到Access資料庫（主要是MDB格式）的方法。主要有三種，分別是PL/SQL、ESFDatabaseMigrationToolkit及ODBC。 PL/SQL 先在PL/SQL中將數據表導出為EXCEL或CSV，再用ACCESS遷入到MDB資料庫中。 圖 ...
  • MySQL知識點
    1.WITH ROLLUP：在分組的基礎上進行統計數據。 例子：首先在name欄位上進行分組，然後在分組的基礎上進行統計 參數說明：如果a==null,則選擇b；如果b==null,則選擇c；如果a!=null,則選擇a；如果a b c 都為null ，則返回為null（沒意義）。 以下實例中如果名 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類