您的位置:

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;