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;