SQL Server觸發器詳解

一、SQL Server觸發器禁止刪除

SQL Server中，我們創建觸發器後，如果不再需要此觸發器，我們可以使用DROP TRIGGER語句進行刪除。但是，這種刪除無法撤銷，如果我們要重新創建相同的觸發器，就需要重新編寫此觸發器了。

為了防止誤操作，我們可以使用系統視圖sys.triggers中的is_ms_shipped列進行判斷。如果is_ms_shipped列的值為1，則說明此觸發器是由SQL Server自帶的，不能刪除。如果is_ms_shipped列的值為0，則說明此觸發器是用戶自己創建的，可以刪除。

--判斷是否可以刪除觸發器
SELECT name, is_ms_shipped
FROM sys.triggers
WHERE name = 'trg_example'

二、SQL Server觸發器寫法

SQL Server中，我們可以使用CREATE TRIGGER語句來創建觸發器。CREATE TRIGGER語句的一般語法如下：

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT | UPDATE | DELETE}
AS
BEGIN
    --觸發器的操作語句
END

其中，trigger_name為觸發器名稱，table_name為觸發器所在的表名稱，{INSERT | UPDATE | DELETE} 表示觸發器的觸發事件，可以是INSERT、UPDATE、DELETE中的一個或多個。在AS和BEGIN之間，我們可以編寫觸發器的操作語句。

三、觸發器SQL Server

SQL Server中，觸發器主要有三種類型：INSERT、UPDATE和DELETE。它們分別在添加、修改和刪除表數據時觸發。

觸發器在SQL Server中的執行順序為：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。其中，BEFORE觸發器在觸發事件發生之前執行，AFTER觸發器在觸發事件發生之後執行。

四、SQL Server觸發器的作用

SQL Server觸發器的作用是在資料庫發生特定事件時，自動執行一個操作。觸發器可以用於實現數據完整性約束、複雜業務邏輯等。

例如，我們可以使用觸發器對某個表的數據進行審計。當有新數據被插入、更新或刪除時，觸發器會自動記錄下新數據的變化情況。

五、SQL Server觸發器在哪

在SQL Server中，我們可以使用以下兩種方式查看觸發器：

• 使用SQL Server Management Studio（SSMS）的Object Explorer，找到相應的資料庫，然後展開「Tables」文件夾，右鍵單擊要查看的表，選擇「Properties」選項卡，然後在「Triggers」選項卡下查看此表的觸發器。
• 使用以下T-SQL語句查詢sys.triggers視圖，可以列出所有表的觸發器。

SELECT t.name AS trigger_name, 
       s.name AS table_schema, 
       OBJECT_NAME(parent_id) AS table_name
FROM sys.triggers AS t
INNER JOIN sys.tables AS st
    ON t.parent_id = st.object_id
INNER JOIN sys.schemas AS s
    ON st.schema_id = s.schema_id
ORDER BY s.name, OBJECT_NAME(parent_id), t.name;

六、SQL Server觸發器三種

SQL Server中，觸發器一般有三種類型：BEFORE INSERT、AFTER INSERT和INSTEAD OF INSERT。其中，BEFORE INSERT觸發器在數據插入之前執行，AFTER INSERT觸發器在數據插入之後執行，INSTEAD OF INSERT觸發器在數據插入之前替代原始的INSERT操作執行。

BEFORE和AFTER觸發器常用於觸發事件之前或之後的數據校驗、記錄日誌、更新相關表等操作。而INSTEAD OF觸發器常用於更新視圖或分解表之類的操作。

七、SQL Server觸發器語法

SQL Server觸發器的語法格式如下：

CREATE TRIGGER trigger_name
ON table_name
[ WITH ENCRYPTION ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
    sql_statement [ ; ]
GO

其中，WITH ENCRYPTION表示使用加密保護存儲過程的代碼。

FOR、AFTER、INSTEAD OF表示觸發器執行的時機，可以為INSERT、UPDATE、DELETE。

WITH APPEND表示允許多個觸發器放置於同一事件中。

NOT FOR REPLICATION表示此觸發器不參與數據複製過程。

八、SQL Server觸發器在哪找

SQL Server本身不提供觸發器模板，但是我們可以在互聯網上找到很多觸發器模板。在使用這些模板時，我們需要對其進行一定的修改和適應。

例如，我們可以在以下網站上找到觸發器模板：

• https://www.codeproject.com/Articles/38629/The-SQL-Server-2005-Template-Trigger
• https://www.mssqltips.com/sqlservertip/1488/useful-sql-server-triggers/

九、SQL Server觸發器INSERT回滾

SQL Server觸發器中，我們可以使用ROLLBACK語句將INSERT操作回滾到之前的狀態。通常情況下，我們不建議在觸發器中使用ROLLBACK語句，因為它可能會導致數據不一致。

例如，在下面的代碼中，如果INSERT操作發生失敗，那麼ROLLBACK TRANSACTION語句將會回滾到事務的初始狀態，也就是觸發器執行之前的狀態。但是，如果在事務的其他部分中發生了修改，那麼這些修改也將被回滾，導致數據不一致。

CREATE TRIGGER trg_example
ON dbo.example
FOR INSERT
AS
BEGIN
    BEGIN TRY
        
        --為避免不必要的死鎖，使用SELECT語句佔用鎖
        SELECT COUNT(1) FROM dbo.example WITH (XLOCK, HOLDLOCK)
        
        --INSERT操作
        INSERT INTO dbo.example_log (log_time, message)
        VALUES (GETDATE(), 'New record inserted');
        
    END TRY
    
    BEGIN CATCH
        --回滾INSERT操作
        ROLLBACK TRANSACTION;
    END CATCH;
END;

十、SQL Server觸發器實例

以下是一個SQL Server觸發器實例，當有新數據插入或刪除時，它會自動記錄下此操作的用戶和時間，保存在另一個表中。

CREATE TABLE dbo.example (
    ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name    VARCHAR(50) NOT NULL,
    Email   VARCHAR(50) NOT NULL,
    Phone   VARCHAR(50) NULL
);

CREATE TABLE dbo.example_log (
    log_id  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    log_time    DATETIME NOT NULL,
    user_name   VARCHAR(50) NOT NULL,
    message VARCHAR(50) NOT NULL
);

CREATE TRIGGER trg_example
ON dbo.example
FOR INSERT, DELETE
AS
BEGIN
    --為避免不必要的死鎖，使用SELECT語句佔用鎖
    SELECT COUNT(1) FROM dbo.example WITH (XLOCK, HOLDLOCK)
    
    --記錄操作信息
    IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
    BEGIN
        INSERT INTO dbo.example_log (log_time, user_name, message)
        SELECT GETDATE(), SUSER_SNAME(), 'New record inserted' FROM inserted;
    END
    ELSE IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
    BEGIN
        INSERT INTO dbo.example_log (log_time, user_name, message)
        SELECT GETDATE(), SUSER_SNAME(), 'Record deleted' FROM deleted;
    END
END;