SQL觸發器之常用語句有哪些

這篇文章給大家分享的是有關SQL觸發器之常用語句有哪些的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

一、創建一個簡單的觸發器

CREATE TRIGGER 觸發器名稱
ON 表名
FOR INSERT、UPDATE 或 DELETE
AS
    T-SQL 語句

注意：觸發器名稱是不加引號的。

二、刪除觸發器

drop trigger 觸發器名稱

刪除多個觸發器：

drop trigger 觸發器名稱,觸發器名稱

三、重命名觸發器

用查詢分析器重命名

exec sp_rename 原名稱, 新名稱

sp_rename 是 SQL Server  自帶的一個存儲過程，用于更改當前數據庫中用戶創建的對象的名稱，如表名、列表、索引名等。

四、INSERT、UPDATE 或 DELETE

INSTEAD OF

執行觸發器語句，但不執行觸發觸發器的 SQL 語句，比如試圖刪除一條記錄時，將執行觸發器指定的語句，此時不再執行 delete 語句。例：

create trigger f
on tbl
instead of delete
as
    insert into Logs...

IF UPDATE(列名)

檢查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：

create trigger f
on tbl
for update
as
    if update(status) or update(title)
        sql_statement --更新了 status 或 title 列

inserted、deleted

這是兩個虛擬表，inserted 保存的是 insert 或 update 之后所影響的記錄形成的表，deleted 保存的是 delete 或 update 之前所影響的記錄形成的表。例：

create trigger tbl_delete
on tbl
for delete
as
    declare @title varchar(200)
    select @title=title from deleted
    insert into Logs(logContent) values('刪除了 title 為：' + title + '的記錄')

說明：如果向 inserted 或 deleted 虛擬表中取字段類型為 text、image 的字段值時，所取得的值將會是 null。

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

use 數據庫名
go
select * from sysobjects where xtype='TR'
sysobjects 保存著數據庫的對象，其中 xtype 為 TR 的記錄即為觸發器對象。在 name 一列，我們可以看到觸發器名稱。

六、sp_helptext 查看觸發器內容

use 數據庫名
go
exec sp_helptext '觸發器名稱'

七、sp_helptrigger 用于查看觸發器的屬性

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

例：

use 數據庫名
go
exec sp_helptrigger tbl

八、遞歸、嵌套觸發器

遞歸分兩種，間接遞歸和直接遞歸。我們舉例解釋如下，假如有表1、表2名稱分別為 T1、T2，在 T1、T2 上分別有觸發器 G1、G2。

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

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

嵌套觸發器

類似于間接遞歸，間接遞歸必然要形成一個環，而嵌套觸發器不一定要形成一個環，它可以 T1->T2->T3...這樣一直觸發下去，最多允許嵌套 32 層。

設置直接遞歸

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

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

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

設置間接遞歸、嵌套

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

T-SQL：exec sp_configure 'nested triggers', 0 --第二個參數為 1 則為允許

EM：注冊上點右鍵->屬性->服務器設置。

九、觸發器回滾

我們看到許多注冊系統在注冊后都不能更改用戶名，但這多半是由應用程序決定的， 如果直接打開數據庫表進行更改，同樣可以更改其用戶名，在觸發器中利用回滾就可以巧妙地實現無法更改用戶名。

use 數據庫名
go
create trigger tr
on 表名
for update
as
    if update(userName)
        rollback tran

關鍵在最后兩句，其解釋為：如果更新了 userName 列，就回滾事務。

十、禁用、啟用觸發器

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

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

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

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

十一、判斷插入修改刪除

create TRIGGER [updatetest] ON [dbo].[TEST] 
FOR INSERT, UPDATE, DELETE 
AS
begin
    DECLARE
    @IsInsert bit,
    @IsUpdate bit,
    @IsDelete bit
    IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
        SET @IsInsert = 1
    ELSE
        SET @IsInsert = 0
     
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @IsUpdate = 1
    ELSE
        SET @IsUpdate = 0
     
    IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @IsDelete = 1
    ELSE
        SET @IsDelete = 0 
     
    if (@IsUpdate=1)
       PRINT 'updated '
     if (@IsInsert=1)
       PRINT 'insert '
     if (@IsDelete=1)
       PRINT 'delete 'end

感謝各位的閱讀！關于“SQL觸發器之常用語句有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！