觸發器
概述
? ? 觸發器為特殊類型的存儲過程，可在執行語言事件時自動生效。SQL Server 包括三種常規類型的觸發器：DML 觸發器、DDL 觸發器和登錄觸發器。
當服務器或數據庫中發生數據定義語言 (DDL) 事件時將調用 DDL 觸發器。登錄觸發器將為響應 LOGON 事件而激發存儲過程。與 SQL Server 實例建立用戶會話時將引發此事件。
當數據庫中發生數據操作語言 (DML) 事件時將調用 DML 觸發器。DML 事件包括在指定表或視圖中修改數據的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發器可以查詢其他表，還可以包含復雜的 Transact-SQL 語句。將觸發器和觸發它的語句作為可在觸發器內回滾的單個事務對待。如果檢測到錯誤（例如，磁盤空間不足），則整個事務即自動回滾。
?
步驟
?? 本文主要講述DML觸發器，DML觸發器有兩種：AFTER，INSTEAD OF觸發器，同時DML 觸發器使用 deleted 和 inserted 邏輯（概念）表。 它們在結構上類似于定義了觸發器的表，即對其嘗試執行了用戶操作的表。 在 deleted 和 inserted 表保存了可能會被用戶更改的行的舊值或新值。
- 對于INSERT 操作，inserted保留新增的記錄，deleted無記錄
- 對于DELETE 操作，inserted無記錄，deleted保留被刪除的記錄
- 對于UPDATE操作，inserted保留修改后的記錄，deleted保留修改前的記錄
? ? 一.語法
CREATE
TRIGGER
[
schema_name .
]
trigger_name
ON
{
table
|
view
}
[
WITH <dml_trigger_option> [ ,...n
]
]
{
FOR
|
AFTER
|
INSTEAD
OF
}
{
[
INSERT
]
[
,
]
[
UPDATE
]
[
,
]
[
DELETE
]
}
AS
{ sql_statement
[
;
]
[
,...n
]
[
;
]
>
}
?
? ? ?二.創建表
CREATE
TABLE
Class
(Cno
INT
PRIMARY
KEY
,
Cname
nvarchar
(
20
)
not
null
)
go
CREATE
TABLE
Student
(SNO
INT
PRIMARY
KEY
IDENTITY
(
1
,
1
),
Sname
CHAR
(
10
)
not
null
,
Age
int
not
null
,
Sex
char
(
2
)
not
null
,
Cno
int
NOT
NULL
)
ALTER
TABLE
Student
ADD
CONSTRAINT
FK_SNO_Cno
FOREIGN
KEY
(Cno)
REFERENCES
Class(Cno)
go
AFTER觸發器
AFTER 指定 DML 觸發器僅在觸發 SQL 語句中指定的所有操作都已成功執行時才被觸發。 所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成。
如果僅指定 FOR 關鍵字，則 AFTER 為默認值。
不能對視圖定義 AFTER 觸發器
- insert觸發
當向Class表中插入一條數據時，獲取插入的cno，同時向Student表中插入一條數據
IF OBJECT_ID('
TR_Class_insert
','TR') IS NOT NULL
DROP TRIGGER
TR_Class_insert
G0
CREATE
TRIGGER
TR_Class_insert
on
Class
AFTER
INSERT
AS
BEGIN
DECLARE
@Cno
INT
SELECT
@Cno
=
Cno
FROM
inserted
--
--獲取插入的數據CNO
INSERT
INTO
Student(Sname,Age,Sex,Cno)
VALUES
(
'
李明
'
,
20
,
'
男
'
,
@Cno
)
END
go
INSERT
INTO
Class
SELECT
101
,
'
一班
'
SELECT
*
FROM
Class
SELECT
*
FROM
Student
- UPDATE觸發
獲取修改的Age值，如果Age為負數則執行回滾操作，否則輸出修改前后的Age值
IF
OBJECT_ID
(
'
TR_Student_update
'
,
'
TR
'
)
IS
NOT
NULL
DROP
TRIGGER
TR_Student_update
GO
CREATE
TRIGGER
TR_Student_update
on
Student
AFTER
UPDATE
AS
BEGIN
DECLARE
@Age_old
int
,
@Age_new
int
SELECT
@Age_old
=
Age
from
deleted
--
--獲取修改前的
SELECT
@Age_new
=
Age
FROM
inserted
--
--獲取更改后的數據
if
@Age_new
<
0
begin
print
'
年齡不能為負數
'
rollback
;
end
else
BEGIN
print
@Age_old
print
@Age_new
END
END
go
update
Student
set
Age
=-
20
where
SNO
=
1
SELECT
*
FROM
Class
SELECT
*
FROM
Student
update
Student
set
Age
=
25
where
SNO
=
1
SELECT
*
FROM
Class
SELECT
*
FROM
Student
- ?delete觸發
獲取被刪除的數據，返回錯誤提示，該步驟正好驗證了“所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成”，該步驟不會返回制定的錯誤提示，因為被刪除的數據作用于外鍵約束，所以先于觸發器操作執行外鍵約束，返回約束錯誤提示，并執行回滾.
IF
OBJECT_ID
(
'
TR_Class_delete
'
,
'
TR
'
)
IS
NOT
NULL
DROP
TRIGGER
TR_Class_delete
GO
CREATE
TRIGGER
TR_Class_delete
on
Class
AFTER
DELETE
AS
BEGIN
DECLARE
@Cno
int
SELECT
@Cno
=
Cno
from
DELETED
--
-獲取被刪除的記錄
IF
@Cno>0
begin
RAISERROR
(
'
數據不能被刪除，被用于外鍵約束
'
,
16
,
10
);
rollback
--
--執行回滾操作
end
END
SELECT
*
FROM
Class
SELECT
*
FROM
Student
DELETE
FROM
Class
where
CNO
=
101
SELECT
*
FROM
Class
SELECT
*
FROM
Student
對Student表建立外鍵約束，用于級聯操作 ON DELETE，對于表的級聯刪除更新操作這里就不講述了
刪除之前創建的外鍵約束,并創建具有級聯更新刪除操作的外鍵約束
alter
table
student
drop
constraint
FK_SNO_Cno
ALTER
TABLE
Student
ADD
CONSTRAINT
FK_SNO_Cno
FOREIGN
KEY
(Cno)
REFERENCES
Class (Cno)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE
再執行刪除語句，返回制定錯誤提示“數據不能被刪除，被用于外鍵約束”并執行回滾操作
DELETE
FROM
Class
where
CNO
=
101
SELECT
*
FROM
Class
SELECT
*
FROM
Student
?
INSTEAD OF觸發器
指定執行 DML 觸發器而不是觸發 SQL 語句，因此，其優先級高于觸發語句的操作。? 不能為 DDL 或登錄觸發器指定 INSTEAD OF。
對于表或視圖，每個 INSERT、UPDATE 或 DELETE 語句最多可定義一個 INSTEAD OF 觸發器。? 但是，可以為具有自己的 INSTEAD OF 觸發器的多個視圖定義視圖。
INSTEAD OF 觸發器不可以用于使用 WITH CHECK OPTION 的可更新視圖。? 如果將 INSTEAD OF 觸發器添加到指定了 WITH CHECK OPTION 的可更新視圖中，則 SQL Server 將引發錯誤。 ? 用戶須用 ALTER VIEW 刪除該選項后才能定義 INSTEAD OF 觸發器
對于 INSTEAD OF 觸發器，不允許對具有指定級聯操作 ON DELETE 的引用關系的表使用 DELETE 選項。? 同樣，也不允許對具有指定級聯操作 ON UPDATE 的引用關系的表使用 UPDATE 選項
- ?Insert 觸發
--
-----insert 觸發
--
--刪除已有的instead of觸發器
declare
@name
nvarchar
(
100
)
select
@name
=
name
from
sys.triggers
where
object_name
(parent_id)
=
'
student
'
and
is_instead_of_trigger
=
1
set
@name
=
'
drop trigger
'
+
@name
exec
(
@name
)
IF
OBJECT_ID
(
'
TR_Student_instead_insert
'
,
'
TR
'
)
IS
NOT
NULL
DROP
TRIGGER
TR_Student_instead_insert
GO
CREATE
TRIGGER
TR_Student_instead_insert
on
Student
INSTEAD
OF
insert
AS
BEGIN
SELECT
*
into
T_back
from
inserted
--
--獲取即將插入的數據
END
select
*
from
Student
select
*
from
Class
INSERT
INTO
Student(Sname,Age,Sex,Cno)
values
(
'
張三
'
,
23
,
'
男
'
,
102
)
select
*
from
T_back
?
- ?delete觸發
創建觸發器失敗，因為之前創建外鍵約束時添加了on delete cascade
IF
OBJECT_ID
(
'
TR_Student_instead_delete
'
,
'
TR
'
)
IS
NOT
NULL
DROP
TRIGGER
TR_Student_instead_delete
GO
CREATE
TRIGGER
TR_Student_instead_delete
on
Student
INSTEAD
OF
DELETE
AS
BEGIN
DECLARE
@Cno
int
SELECT
@Cno
=
Cno
from
DELETED
--
-獲取被刪除的記錄
IF
EXISTS
(
SELECT
*
FROM
Class
where
Cno
=
@cno
)
begin
rollback
--
--執行回滾操作
RAISERROR
(
'
數據不能被刪除，被用于外鍵約束1
'
,
16
,
10
);
end
END
消息
2113
，級別
16
，狀態
1
，過程 TR_Student_instead_delete，第
10
行
因為表
'
Student
'
的
FOREIGN
KEY
使用級聯
DELETE
或
UPDATE
，所以無法對該表 創建 INSTEAD
OF
DELETE
或 INSTEAD
OF
UPDATE
TRIGGER
'
TR_Student_instead_delete
'
。
重建外鍵約束，刪除級聯
alter
table
student
drop
constraint
FK_SNO_Cno
ALTER
TABLE
Student
ADD
CONSTRAINT
FK_SNO_Cno
FOREIGN
KEY
(Cno)
REFERENCES
Class (Cno)
?
- ?UPDATE觸發
--
---同一張表中只能定義一個instead of 觸發器，刪除表之前創建的instead of 觸發
declare
@name
nvarchar
(
100
)
select
@name
=
name
from
sys.triggers
where
object_name
(parent_id)
=
'
student
'
and
is_instead_of_trigger
=
1
set
@name
=
'
drop trigger
'
+
@name
exec
(
@name
)
IF
OBJECT_ID
(
'
TR_Student_instead_update
'
,
'
TR
'
)
IS
NOT
NULL
DROP
TRIGGER
TR_Student_instead_update
GO
CREATE
TRIGGER
TR_Student_instead_update
on
Student
INSTEAD
OF
update
AS
BEGIN
DECLARE
@Age_del
int
,
@Age_up
int
SELECT
@Age_del
=
Age
from
DELETED
--
-獲取被更改的記錄
SELECT
@Age_up
=
Age
from
Inserted
begin
print
@Age_del
print
@Age_up
select
*
from
Student
--
--查詢數據是否被更改
end
END
--
--查詢更新前的表數據
select
*
from
student
SNO Sname Age Sex Cno
13
李明
22
男
101
update
Student
set
age
=
-2
where
CNO
=
101
----對于前面定義的after觸發器age不能為負數也不會執行，instead of 觸發器高于執行語句，高于after 觸發
SNO Sname Age Sex Cno
13
李明
22
男
101
select
*
from
student
SNO Sname Age Sex Cno
13
李明
22
男
101
(
1
行受影響)
22
-2
(
1
行受影響)
(
1
行受影響)
當表上面定義了instead
of
觸發器，指定執行 DML 觸發器而不是觸發 SQL 語句，因此，其優先級高于觸發語句的操作，而且也不會執行表上面定義的after觸發器
?
?創建帶字段判斷的觸發器， 根據對特定列的 UPDATE 或 INSERT 修改來執行某些操作
--
----創建字段更新判斷的update觸發器
ALTER
TABLE
Class
ADD
Address
nvarchar
(
50
)
IF
OBJECT_ID
(
'
TR_Class_Update
'
,
'
TR
'
)
IS
NOT
NULL
DROP
TRIGGER
TR_Class_Update
GO
CREATE
TRIGGER
TR_Class_Update
on
Class
AFTER
UPDATE
AS
BEGIN
IF
UPDATE
(Cname)
or
UPDATE
(Address)
BEGIN
RAISERROR
(
'
數據不能被修改
'
,
16
,
10
)
ROLLBACK
END
END
SELECT
*
FROM
Class
UPDATE
Class
set
Address
=
'
5棟101
'
where
Cno
=
101
SELECT
*
FROM
Class
?
總結
? ?
雖然觸發器功能強大，輕松可靠地實現許多復雜的功能，同時過多觸發器會造成數據庫及應用程序的維護困難，同時對觸發器過分的依賴，勢必影響數據庫的結構，同時增加了維護的復雜程序.
?
|
備注： ??? 作者： 沉寂的石頭 ??? 博客： http://www.cnblogs.com/chenmh 歡迎大家轉載，但轉載時必須注明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責任的權利。 歡迎大家拍磚 |
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
