sqlserver觸發器怎麼寫?概念：  觸發器（trigger）是SQL server 提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由事件來觸發，當對一個表進行操作（ insert，delete， update）時就會激活它執行觸發器經常用于加強數據的完整性約束和業務規則等 觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 數據字典中查到，下面我們就來說一說關于sqlserver觸發器怎麼寫?我們一起去了解并探讨一下這個問題吧!

sqlserver觸發器怎麼寫

概念：

  觸發器（trigger）是SQL server 提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由事件來觸發，當對一個表進行操作（ insert，delete， update）時就會激活它執行。觸發器經常用于加強數據的完整性約束和業務規則等。 觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 數據字典中查到。

觸發器和存儲過程的區别:

  觸發器與存儲過程的區别是運行方式的不同，觸發器不能執行EXECUTE語句調用，而是在用戶執行Transact-SQL語句時自動觸發執行而存儲過程需要用戶，應用程序或者觸發器來顯示地調用并執行。

回到頂部

一:觸發器的優點

 1.觸發器是自動的。當對表中的數據做了任何修改之後立即被激活。

 2.觸發器可以通過數據庫中的相關表進行層疊修改。

 3.觸發器可以強制限制。這些限制比用CHECK約束所定義的更複雜。與CHECK約束不同的是，觸發器可以引用其他表中的列。

回到頂部

二:觸發器的作用

 觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一緻性，它能夠對數據庫中的相關表進行級聯修改，提高比CHECK約束更複雜的的數據完整性，并自定義錯誤消息。觸發器的主要作用主要有以下幾個方面：

強制數據庫間的引用完整性

級聯修改數據庫中所有相關的表，自動觸發其它與之相關的操作

跟蹤變化，撤銷或回滾違法操作，防止非法修改數據

返回自定義的錯誤消息，約束無法返回信息，而觸發器可以

觸發器可以調用更多的存儲過程

回到頂部

三:觸發器的分類

 SqlServer包括三種常規類型的觸發器：DML觸發器、DDL觸發器和登錄觸發器。

1.DML(數據操作語言,Data Manipulation Language)觸發器

 DML觸發器是一些附加在特定表或視圖上的操作代碼，當數據庫服務器中發生數據操作語言事件時執行這些操作。SqlServer中的DML觸發器有三種：

insert觸發器:向表中插入數據時被觸發；

delete觸發器：從表中删除數據時被觸發；

update觸發器：修改表中數據時被觸發。

當遇到下列情形時，應考慮使用DML觸發器：

通過數據庫中的相關表實現級聯更改

防止惡意或者錯誤的insert、update和delete操作，并強制執行check約束定義的限制更為複雜的其他限制。

評估數據修改前後表的狀态，并根據該差異采取措施。

2.DDL(數據定義語言,Data Definition Language)觸發器

 DDL觸發器是當服務器或者數據庫中發生數據定義語言(主要是以create,drop,alter開頭的語句)事件時被激活使用，使用DDL觸發器可以防止對數據架構進行的某些更改或記錄數據中的更改或事件操作。

3.登錄觸發器

    登錄觸發器将為響應 LOGIN 事件而激發存儲過程。與 SQL Server 實例建立用戶會話時将引發此事件。登錄觸發器将在登錄的身份驗證階段完成之後且用戶會話實際建立之前激發。因此，來自觸發器内部且通常将到達用戶的所有消息（例如錯誤消息和來自 PRINT 語句的消息）會傳送到 SQL Server 錯誤日志。如果身份驗證失敗，将不激發登錄觸發器。

回到頂部

四：觸發器的工作原理

觸發器觸發時：

系統自動在内存中創建deleted表或inserted表；

隻讀，不允許修改，觸發器執行完成後，自動删除。

inserted表：

臨時保存了插入或更新後的記錄行；

可以從inserted表中檢查插入的數據是否滿足業務需求；

如果不滿足，則向用戶發送報告錯誤消息，并回滾插入操作。　

deleted表：

臨時保存了删除或更新前的記錄行；

可以從deleted表中檢查被删除的數據是否滿足業務需求；

如果不滿足，則向用戶報告錯誤消息，并回滾插入操作。

inserted表和deleted表對照：　

修改操作記錄 inserted表 deleted表

增加(insert)記錄 存放新增的記錄 ............

删除(deleted)記錄 .............. 存放被删除的記錄

修改(update)記錄 存放更新後的記錄 存放更新前的記錄

回到頂部

五:創建觸發器

創建觸發器的語法：

CREATE TRIGGER trigger_name

ON table_name

[WITH ENCRYPTION]

FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]

AS

T-SQL語句

GO

--with encryption 表示加密觸發器定義的sql文本

--delete,insert,update指定觸發器的類型

準備測試數據:

--創建學生表

create table student(

stu_id int identity(1,1) primary key,

stu_name varchar(10),

stu_gender char(2),

stu_age int

)

1.創建insert觸發器

--創建insert觸發器

create trigger trig_insert

on student

after insert

as

begin

if object_id(N'student_sum',N'U') is null--判斷student_sum表是否存在

create table student_sum(stuCount int default(0));--創建存儲學生人數的student_sum表

declare @stuNumber int;

select @stuNumber = count(*)from student;

if not exists (select * from student_sum)--判斷表中是否有記錄

insert into student_sum values(0);

update student_sum set stuCount =@stuNumber; --把更新後總的學生數插入到student_sum表中

end

--測試觸發器trig_insert-->功能是向student插入數據的同時級聯插入到student_sum表中，更新stuCount

--因為是後觸發器，所以先插入數據後，才觸發觸發器trig_insert;

insert into student(stu_name,stu_gender,stu_age)values('呂布','男',30);

select stuCount 學生總人數 from student_sum;

insert into student(stu_name,stu_gender,stu_age)values('貂蟬','女',30);

select stuCount 學生總人數 from student_sum;

insert into student(stu_name,stu_gender,stu_age)values('曹阿瞞','男',40);

select stuCount 學生總人數 from student_sum;

執行上面的語句後，結果如下圖所示：

既然定義了學生總數表student_sum表是向student表中插入數據後才計算學生總數的，所以學生總數表應該禁止用戶向其中插入數據

--創建insert_forbidden,禁止用戶向student_sum表中插入數據

create trigger insert_forbidden

on student_sum

after insert

as

begin

RAISERROR('禁止直接向該表中插入記錄，操作被禁止',1,1)--raiserror 是用于抛出一個錯誤

rollback transaction

end

--觸發觸發器insert_forbidden

insert student_sum (stuCount) values(5);

結果如下：

2.創建delete觸發器

　　用戶執行delete操作，就會激活delete觸發器，從而控制用戶能夠從數據庫中删除數據記錄，觸發delete觸發器後，用戶删除的記錄會被添加到deleted表中，原來表的相應記錄被删除，所以在deleted表中查看删除的記錄。

--創建delete觸發器

create trigger trig_delete

on student

after delete

as

begin

select stu_id as 已删除的學生編号,stu_name stu_gender,stu_age

from deleted

end;

--執行一一條delete語句觸發trig_delete觸發器

delete from student where stu_id=1;

結果如下：

3.創建UPDATE觸發器

　　update觸發器是當用戶在指定表上執行update語句時被調用被調用，這種類型的觸發器用來約束用戶對數據的修改。update觸發器可以執行兩種操作：更新前的記錄存儲在deleted表中，更新後的記錄存儲在inserted表中。

--創建update觸發器

create trigger trig_update

on student

after update

as

begin

declare @stuCount int;

select @stuCount=count(*) from student;

update student_sum set stuCount =@stuCount;

select stu_id as 更新前學生編号,stu_name as 更新前學生姓名 from deleted

select stu_id as 更新後學生編号,stu_name as 更新後學生姓名 from inserted

end

--創建完成，執行一條update語句觸發trig_update觸發器

update student set stu_name='張飛' where stu_id=2;

4.創建替代觸發器

　　與前面介紹的三種after觸發器不同，SqlServer服務器在執行after觸發器的sql代碼後，先建立臨時的inserted表和deleted表，然後執行代碼中對數據庫操作，最後才激活觸發器中的代碼。而對于替代(instead of)觸發器,SqlServer服務器在執行觸發instead of 觸發器的代碼時，先建立臨時的inserted表和deleted表，然後直接觸發instead of觸發器，而拒絕執行用戶輸入的DML操作語句。

--創建instead of 觸發器

create trigger trig_insteadOf

on student

instead of insert

as

begin

declare @stuAge int;

select @stuAge=(select stu_age from inserted)

if(@stuAge >120)

select '插入年齡錯誤' as '失敗原因'

end

創建完成，執行一條insert語句觸發觸發器trig_insteadOf

5.嵌套觸發器介紹

　如果一個觸發器在執行操作時調用了另外一個觸發器，而這個觸發器又接着調用了下一個觸發器，那麼就形成了嵌套觸發器。嵌套觸發器在安裝時就被啟用，但是可以使用系統存儲過程sp_configure禁用和重新啟用嵌套觸發器。

　　嵌套觸發器不一定要形成一個環，它可以 T1->T2->T3...這樣一直觸發下去，最多允許嵌套 32 層。如果嵌套的次數超過限制，那麼該觸發器将被終止，并回滾整個事務，使用嵌套觸發器需要注意以下幾點：

默認情況下，嵌套觸發器配置選項是開啟的。

在同一個觸發器事務中，一個嵌套觸發器不能被觸發兩次。

由于觸發器是一個事務，如果在一系列嵌套觸發器的任意層次中發生錯誤，則整個事務都将取消，而且所有數據回滾。

嵌套是用來保持整個數據庫的完整性的重要功能，但有時可能需要禁用嵌套，如果禁用了嵌套，那麼修改一個觸發器的實現不會再觸發該表上的任何觸發器。在下述情況下，需要禁用嵌套觸發器：

嵌套觸發要求複雜而有理論的設計，級聯修改可能會修改用戶不想涉及的數據。

在一系列嵌套觸發器中的任意點的時間修改操作都會觸發一些觸發器，盡管這時數據庫提供很強的保護功能，但如果以特定的順序更新表，就會産生問題。

使用下列語句禁用嵌套和再次啟用嵌套：

--禁用嵌套

exce sp_configure 'nested triggers',0;

--啟用嵌套

exce sp_configure 'nested triggers',1;

6.遞歸觸發器

　　觸發器的遞歸是指一個觸發器從其内部再一次激活該觸發器，例如update操作激活的觸發器内部還有一條數據表的更新語句，那麼這個更新語句就有可能激活這個觸發器本身，當然，這種遞歸的觸發器内部還會有判斷語句，隻有一定情況下才會執行那個T_SQL語句，否則就成為無線調用的死循環了。

SqlServer中的遞歸觸發器包括兩種：直接遞歸和間接遞歸。

直接遞歸：觸發器被觸發後并執行一個操作，而該操作又使用一個觸發器再次被觸發。

間接遞歸：觸發器被觸發并執行一個操作，而該操作又使另一個表中的某個觸發器被觸發，第二個觸發器使原始表得到更新，從而再次觸發第一個觸發器。

默認情況下，遞歸觸發器選項是禁用的。遞歸觸發器最多隻能遞歸16層，如果遞歸中的第16個觸發器激活了第17個觸發器，則結果與發布的rollback命令一樣，所有數據都将回滾。

我們舉例解釋如下，假如有表1、表2名稱分别為 T1、T2，在 T1、T2 上分别有觸發器 G1、G2。

間接遞歸：對 T1 操作從而觸發 G1，G1 對 T2 操作從而觸發 G2，G2 對 T1 操作從而再次觸發 G1...

直接遞歸：對 T1 操作從而觸發 G1，G1 對 T1 操作從而再次觸發 G1...

設置直接遞歸：

默認情況下是禁止直接遞歸的，要設置為允許有兩種方法：

T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true；

EM：數據庫上點右鍵->屬性->選項。

回到頂部

六:管理觸發器　

1.查看觸發器

(1).查看數據庫中所有的觸發器

--查看數據庫中所有的觸發器

use 數據庫名

go

select * from sysobjects where xtype='TR'

sysobjects 保存着數據庫的對象，其中 xtype 為 TR 的記錄即為觸發器對象。在 name 一列，我們可以看到觸發器名稱。

(2).sp_helptext 查看觸發器内容

use 數據庫名

go

exec sp_helptext '觸發器名稱'

将會以表的樣式顯示觸發器内容。

除了觸發器外，sp_helptext 還可以顯示 規則、默認值、未加密的存儲過程、用戶定義函數、視圖的文本。

(3).sp_helptrigger 用于查看觸發器的屬性

　　sp_helptrigger 有兩個參數：第一個參數為表名；第二個為觸發器類型，為 char(6) 類型，可以是 INSERT、UPDATE、DELETE，如果省略則顯示指定表中所有類型觸發器的屬性。

use 數據庫名

go

exec sp_helptrigger tableName

2.禁用啟用觸發器

　　禁用:alter table 表名 disable trigger 觸發器名稱

　　啟用:alter table 表名 enable trigger 觸發器名稱

　　如果有多個觸發器，則各個觸發器名稱之間用英文逗号隔開。

　　如果把“觸發器名稱”換成“ALL”，則表示禁用或啟用該表的全部觸發器。

3修改觸發器

--修改觸發器語法

ALTER TRIGGER trigger_name

ON table_name

[ WITH ENCRYPTION ]

FOR {[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement;

4.删除觸發器

--語法格式:

DROP TRIGGER { trigger } [ ,...n ]

參數:

trigger: 要删除的觸發器名稱

n:表示可以删除多個觸發器的占位符

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!