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

###推薦學習：###MySQL教學#######

以上是SQL觸發器之常用語句（總結）的詳細內容。更多資訊請關注PHP中文網其他相關文章！