概述
觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器重要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名被直接調(diào)用。
可以用于SQLServer約束，默認值和規(guī)則的完整性檢查。
分類：數(shù)據(jù)操作語言觸發(fā)器、數(shù)據(jù)定義語言觸發(fā)器
-
數(shù)據(jù)操作語言（Data Manipulation Language ，DML）觸發(fā)器
DML觸發(fā)器是一些附加在特定表或視圖上的操作代碼，當數(shù)據(jù)庫服務器中發(fā)生數(shù)據(jù)操作語言事件時執(zhí)行這些操作。
SQLServer中DML觸發(fā)器有三種：INSERT觸發(fā)器、UPDATE觸發(fā)器、DELETE觸發(fā)器。 -
數(shù)據(jù)定義語言（Data Definition Language ，DDL）觸發(fā)器
DDL觸發(fā)器是當服務器或者數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言事件時被激活調(diào)用，使用DDL可以防止對數(shù)據(jù)庫架構(gòu)進行的默寫更改或記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。
創(chuàng)建DML觸發(fā)器
INSERT觸發(fā)器
【例1】在stu_info表上創(chuàng)建一個名稱為Insert_Student的觸發(fā)器，在用戶向stu_info表中插入數(shù)據(jù)時觸發(fā)
CREATE TRIGGER Insert_Student
ON stu_info
AFTER INSERT
AS
BEGIN
IF OBJECT_ID(N'stu_Sum',N'U') IS NULL --判斷stu_Sum表是否存在
CREATE TABLE stu_Sum(number INT DEFAULT 0) --創(chuàng)建存儲學生人數(shù)的stu_Sum表
DECLARE @stuNumber INT
SELECT @stuNumber = COUNT(*) FROM stu_info
IF NOT EXISTS (SELECT * FROM stu_Sum) --判斷表中是否有記錄
INSERT INTO stu_Sum VALUES(0)
UPDATE stu_Sum SET number = @stuNumber --把更新后總的學生人數(shù)插入到stu_Sum表中
END
-- 當創(chuàng)建完觸發(fā)器之后，向stu_info表中插入記錄，觸發(fā)觸發(fā)器的執(zhí)行。執(zhí)行下面的語句。
SELECT COUNT(*) stu_info表中總?cè)藬?shù) FROM stu_info
INSERT INTO stu_info (s_id,s_name,s_score,s_sex) VALUES(20,'白雪',87,'女')
SELECT COUNT(*) stu_info表中總?cè)藬?shù) FROM stu_info
SELECT number AS stu_Sum表中總?cè)藬?shù) FROM stu_Sum
【例2】創(chuàng)建觸發(fā)器，當用戶向stu_Sum表中插入數(shù)據(jù)時，禁止操作
CREATE TRIGGER Insert_forbidden
ON stu_Sum
AFTER INSERT
AS
BEGIN
RAISERROR('不允許直接向該表插入記錄，操作被禁止',1,1)
ROLLBACK TRANSACTION
END
INSERT INTO stu_Sum VALUES(5)
DELETE觸發(fā)器
【例3】創(chuàng)建DELETE觸發(fā)器，用戶對stu_info表執(zhí)行刪除操作后觸發(fā)，并返回刪除的記錄信息
CREATE TRIGGER Delete_Student
ON stu_info
AFTER DELETE
AS
BEGIN
SELECT s_id AS 已刪除學生編號,s_name,s_score,s_sex,s_age
FROM DELETED
END
--創(chuàng)建完成，執(zhí)行一條DELETE語句觸發(fā)該觸發(fā)器，輸入語句如下。
DELETE FROM stu_info WHERE s_id=6;
UPDATE觸發(fā)器
【例4】創(chuàng)建UPDATE觸發(fā)器，用戶對stu_info表執(zhí)行更新操作后觸發(fā)，并返回更新的記錄信息
CREATE TRIGGER Update_Student
ON stu_info
AFTER UPDATE
AS
BEGIN
DECLARE @stuCount INT;
SELECT @stuCount = COUNT(*) FROM stu_info;
UPDATE stu_Sum SET number = @stuCount;
SELECT s_id AS 更新前學生編號 ,s_name AS 更新前學生姓名 FROM DELETED
SELECT s_id AS 更新后學生編號 ,s_name AS 更新后學生姓名 FROM INSERTED
END
-- 創(chuàng)建完成，執(zhí)行一條UPDATE語句觸發(fā)該觸發(fā)器，輸入語句如下。
UPDATE stu_info SET s_name='張懿' WHERE s_id=1;
替代觸發(fā)器
【例5】創(chuàng)建INSTEAD OF觸發(fā)器，當用戶插入到stu_info表中的學生記錄中的成績大于100分時，拒絕插入，同時提示“插入成績錯誤”的信息。
CREATE TRIGGER InsteadOfIinsert_Student
ON stu_info
INSTEAD OF INSERT
AS
BEGIN
DECLARE @stuScore INT;
SELECT @stuScore = (SELECT s_score FROM inserted)
If @stuScore > 100
SELECT '插入成績錯誤' AS 失敗原因
END
-- 創(chuàng)建完成，執(zhí)行一條INSERT語句觸發(fā)該觸發(fā)器，輸入語句如下。
INSERT INTO stu_info (s_id,s_name,s_score,s_sex)
VALUES(21,'周鴻',110,'男');
SELECT * FROM stu_info;
創(chuàng)建DDL觸發(fā)器
創(chuàng)建服務器作用域的DDL觸發(fā)器，需要指定ALL SERVER參數(shù)。
【例6】創(chuàng)建數(shù)據(jù)庫作用域的DDL觸發(fā)器，拒絕用戶對數(shù)據(jù)庫中表的刪除和修改操作
CREATE TRIGGER DenyDelete_Student
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
PRINT '用戶沒有權(quán)限執(zhí)行刪除操作！'
ROLLBACK TRANSACTION
END
DROP TABLE stu_info
【例7】創(chuàng)建數(shù)據(jù)庫作用域的DDL觸發(fā)器，拒絕用戶對數(shù)據(jù)庫中表的刪除和修改操作
CREATE TRIGGER DenyCreate_AllServer
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE
AS
BEGIN
PRINT '用戶沒有權(quán)限創(chuàng)建或修改服務器上的數(shù)據(jù)庫！'
ROLLBACK TRANSACTION
END
-- 執(zhí)行下面的語句來測試觸發(fā)器的執(zhí)行過程。
CREATE DATABASE test_db
管理觸發(fā)器
查看觸發(fā)器
【例8】使用sp_helptext查看Insert_student觸發(fā)器的信息
sp_helptext Insert_student
修改觸發(fā)器
和創(chuàng)建的語法格式幾乎相同
【例9】修改Insert_student觸發(fā)器，將INSERT觸發(fā)器修改為DELETE觸發(fā)器
ALTER TRIGGER Insert_Student
ON stu_info
AFTER DELETE
AS
BEGIN
IF OBJECT_ID(N'stu_Sum',N'U') IS NULL --判斷stu_Sum表是否存在
CREATE TABLE stu_Sum(number INT DEFAULT 0); --創(chuàng)建存儲學生人數(shù)的stu_Sum表
DECLARE @stuNumber INT;
SELECT @stuNumber = COUNT(*) FROM stu_info;
IF NOT EXISTS (SELECT * FROM stu_Sum)
INSERT INTO stu_Sum VALUES(0);
UPDATE stu_Sum SET number = @stuNumber; --把更新后總的學生人數(shù)插入到stu_Sum表中
END
刪除觸發(fā)器
當觸發(fā)器不再需要時，可以將其刪除，刪除不會影響其操作的數(shù)據(jù)表，而當某個表被刪除時，該表上的觸發(fā)器也同時被刪除。
【例10】使用DROP TRIGGE語句刪除Insert_student觸發(fā)器
DROP TRIGGER Insert_student;
【例11】刪除服務器作用域的觸發(fā)器DenyCreate_AllServer
DROP TRIGGER DenyCreate_AllServer ON ALL Server;
啟用和禁用觸發(fā)器
【例12】禁止使用Update_Student觸發(fā)器
ALTER TABLE stu_info
DISABLE TRIGGER Update_student
-- DISABLE TRIGGER Update_student ON stu_info
【例13】禁止使用數(shù)據(jù)庫作用域的觸發(fā)器DenyDelete_student
DISABLE TRIGGER DenyDelete_student ON DATABASE;
【例14】啟用Update_Student觸發(fā)器
ALTER TABLE stu_info
ENABLE TRIGGER Update_student
-- ENABLE TRIGGER Update_student ON stu_info
【例15】啟用數(shù)據(jù)庫作用域的觸發(fā)器DenyDelete_student
ENABLE TRIGGER DenyDelete_student ON DATABASE;
問題
使用觸發(fā)器需要注意的問題是什么？
在使用觸發(fā)器時，對相同的表、事件只能創(chuàng)建一個觸發(fā)器。
比如對表account創(chuàng)建了一個after insert觸發(fā)器，那么如果對表account再次創(chuàng)建一個after insert觸發(fā)器，將會報錯。此時只可以在表account上創(chuàng)建after insert或者insterad of update類型的觸發(fā)器。
靈活地運用觸發(fā)器將為操作省去很多麻煩。
