程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SqlServer觸發器詳解,sqlserver觸發器

SqlServer觸發器詳解,sqlserver觸發器

編輯:更多數據庫知識

SqlServer觸發器詳解,sqlserver觸發器


     觸發器(trigger)是SQL server 提供給程序員和數據分析員來保證數據完整性的一種方法,它是與表事件相關的特殊的存儲過程,它的執行不是由程序調用,也不是手工啟動,而是由事件來觸發,比如當對一個表進行操作( insert,delete, update)時就會激活它執行。

     觸發器經常用於加強數據的完整性約束和業務規則等。 觸發器可以從 DBA_TRIGGERS ,USER_TRIGGERS 數據字典中查到。SQL3的觸發器是一個能由系統自動執行對數據庫修改的語句。

      觸發器可以查詢其他表,而且可以包含復雜的SQL語句。它們主要用於強制服從復雜的業務規則或要求。例如:您可以根據客戶當前的帳戶狀態,控制是否允許插入新訂單。

     觸發器也可用於強制引用完整性,以便在多個表中添加、更新或刪除行時,保留在這些表之間所定義的關系。然而,強制引用完整性的最好方法是在相關表中定義主鍵和外鍵約束。如果使用數據庫關系圖,則可以在表之間創建關系以自動創建外鍵約束。

     觸發器與存儲過程的唯一區別是觸發器不能執行EXECUTE語句調用,而是在用戶執行Transact-SQL語句時自動觸發執行。

     查詢數據庫中所有觸發器:

select * from sysobjects where xtype='TR'

1、語法

create trigger [shema_name . ] trg_name
on { table | view }
[ with encryption ]
{ for | after | instead of }
{ insert , update , delete }
as
sql_statement

insert觸發器實例

create trigger test
on al
for insert
as
declare @id int,@uid int,@lid int,@result char
select @id=id,@uid=uid,@lid=lid,@result=result from inserted
if(@lid=4)
begin
 update al set uid=99 where id=@id
 print 'lid=4時自動修改用戶id為99'
end

update觸發器實例

create trigger test_update
on al
 for update
as
 declare @oldid int,@olduid int,@oldlid int,@newid int,@newuid int,@newlid int
 select @oldid=id,@olduid=uid,@oldlid=lid from deleted;
 select @newid=id,@newuid=uid,@newlid=lid from inserted
 if(@newlid>@oldlid)
 begin
 print 'newlid>oldid'
 rollback tran;
 end
 else
 print '修改成功'

delete觸發器實例

create trigger test_delete
on al
for delete
as
declare @did int,@duid int,@dlid int
select @did=id,@duid=uid,@dlid=lid from deleted
if(exists(select * from list where @dlid=id))
begin
print '無法刪除'
rollback tran;
end
else
print '刪除成功'

圖文介紹觸發器

數據庫運行環境SqlServer2005

觸發器(trigger)是個特殊的存儲過程,它的執行不是由程序調用,也不是手工啟動,而是由事件來觸發,當對一個表進行操作( insert,delete, update)時就會激活它執行,觸發器經常用於加強數據的完整性約束和業務規則等。其實往簡單了說,就是觸發器就是一個開關,負責燈的亮與滅,你動了,它就亮了,就這個意思。

觸發器的分類

1 DML( 數據操縱語言 Data Manipulation Language)觸發器:是指觸發器在數據庫中發生DML事件時將啟用。DML事件即指在表或視圖中修改數據的insert、update、delete語句。

2 DDL(數據定義語言 Data Definition Language)觸發器:是指當服務器或數據庫中發生(DDL事件時將啟用。DDL事件即指在表或索引中的create、alter、drop語句也。

3 登陸觸發器:是指當用戶登錄SQL SERVER實例建立會話時觸發。

DML觸發器介紹

1 在SQL SERVER 2008中,DML觸發器的實現使用兩個邏輯表DELETED和INSERTED。這兩個表是建立在數據庫服務器的內存中,我們只有只讀的權限。DELETED和INSERED表的結構和觸發器所在的數據表的結構是一樣的。當觸發器執行完成後,它們也就會被自動刪除:INSERED表用於存放你在操件insert、update、delete語句後,更新的記錄。比如你插入一條數據,那麼就會把這條記錄插入到INSERTED表:DELETED表用於存放你在操作 insert、update、delete語句前,你創建觸發器表中數據庫。

2 觸發器可通過數據庫中的相關表實現級聯更改,可以強制比用CHECK約束定義的約束更為復雜的約束。與 CHECK 約束不同,觸發器可以引用其它表中的列,例如觸發器可以使用另一個表中的 SELECT 比較插入或更新的數據,以及執行其它操作。觸發器也可以根據數據修改前後的表狀態,再行采取對策。一個表中的多個同類觸發器(INSERT、UPDATE 或 DELETE)允許采取多個不同的對策以響應同一個修改語句。

3 與此同時,雖然觸發器功能強大,輕松可靠地實現許多復雜的功能,為什麼又要慎用?過多觸發器會造成數據庫及應用程序的維護困難,同時對觸發器過分的依賴,勢必影響數據庫的結構,同時增加了維護的復雜程序。

觸發器步驟詳解

1 首先,我們來嘗試創建一個觸發器,要求就是在AddTable這個表上創建一個Update觸發器,語句為:

create trigger mytrigger on AddTable
for update

2 然後就是sql語句的部分了,主要是如果發生update以後,要求觸發器觸發一個什麼操作。這裡的意思就是如果出現update了,觸發器就會觸發輸出:the table was updated!---By 小豬也無奈。

3 接下來我們來將AddTable表中的數據執行一個更改的操作:

4 執行後,我們會發現,觸發器被觸發,輸出了我們設置好的文本:

5 那觸發器創建以後呢,它就正式開始工作了,這時候我們需要更改觸發器的話,只需要將開始的create創建變為alter,然後修改邏輯即可:

6 如果我們想查看某一個觸發器的內容,直接運行:exec sp_helptext [觸發器名]

7 如果我想查詢當前數據庫中有多少觸發器,以方便我進行數據庫維護,只需要運行:

select * from sysobjects where xtype='TR'

8 我們如果需要關閉或者開啟觸發器的話,只需要運行:

disable trigger [觸發器名] on database --禁用觸發器

enable trigger [觸發器名] on database --開啟觸發器


9 那觸發器的功能雖大,但是一旦觸發,恢復起來就比較麻煩了,那我們就需要對數據進行保護,這裡就需要用到rollback數據回滾~

10 第九步的意思就是查詢AddTable表,如果裡面存在TableName=newTable的,數據就回滾,觸發器中止,那我們再進行一下測試,對AddTable表進行更改,發現,觸發update觸發器之後,因為有數據保護,觸發器中止:

注意事項

禁用和開啟觸發器都需要一定的權限,如果權限不夠是無法進行操作的。

注意運行後的錯誤提示,對於糾正錯誤是很有幫助的。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved