SQL sever中的触发器

Posted on by hackdl

目录

一、触发器概念

二、触发器优缺点

2.1优点：

2.2缺点：

三、触发器种类

四、触发器创建

4.1创建DML触发器

4.2创建DDL触发器

4.3创建登录触发器

五、触发器管理

5.1查看触发器

5.1.1.使用sp_helptext存储过程查看触发器

5.1.2.获取数据库中触发器的信息

5.2修改触发器

5.2.1修改DML触发器

5.2.2修改DDL触发器

5.2.3修改登录触发器

5.3重命名触发器

5.4禁用与启用触发器

5.4.1禁用触发器

5.4.2启用触发器

5.5删除触发器

5.5.1.DROP TRIGGER语句删除触发器

5.5.2.SQL Server Management Studio手动删除触发器

六、触发器应用场景

一、触发器概念

Microsoft SQL Server提供两种主要机制来强制使用业务规则和数据完整性，即约束和触发器。
触发器是一种特殊类型的存储过程，当指定表中的数据发生变化时触发器自动生效。它与表紧密
相连，可以看作是表定义的一部分。触发器不能通过名称被直接调用，更不允许设置参数。
触发器是一种特殊类型的存储过程，它会在数据库表中的数据发生特定事件时自动执行。这些事件可以是数据的插入、更新或删除操作。触发器可以用于实现数据完整性、业务规则和复杂的数据处理逻辑。

触发器由两部分组成：触发事件和触发操作。

触发事件定义了何时触发触发器，可以是在数据插入、更新或删除之前或之后触发。触发操作是在触发事件发生时执行的一系列 SQL 语句。
触发器可以与特定的表相关联，并在关联表的数据发生更改时执行。它们可以用于强制实施特定的业务规则，例如约束条件、数据验证和引用完整性。触发器还可以用于日志记录、审计和数据同步等任务。它们允许开发人员跟踪和控制数据库中的操作，以确保数据的一致性和准确性。
SQL Server 2008 支持多个触发器类型，包括：

  • 插入触发器（INSERT Trigger）：在向表中插入新行之前或之后触发。
  • 更新触发器（UPDATE Trigger）：在更新表中的行之前或之后触发。
  • 删除触发器（DELETE Trigger）：在从表中删除行之前或之后触发。
  • 合并触发器（MERGE Trigger）：在执行 MERGE 语句时触发。

在SQL Server中一张表可以有多个触发器，每个表最多可以有一个触发器与每种触发事件相关联。也就是对于每个表，最多可以定义三个触发器，分别是一个插入触发器、一个更新触发器和一个删除触发器：

  1. INSERT触发器：当在表中插入新行时触发。
  2. UPDATE触发器：当在表中更新现有行时触发。
  3. DELETE触发器：当从表中删除行时触发。

用户可以使用NSERT、UPDATE或DELETE语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。触发器可以包含复杂的Transact-SQL语句。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。

但是每个触发器类型只能与表的对应事件相关联，不允许定义多个相同类型的触发器。虽然每个表最多只能有一个触发器与每种触发事件相关联，但是该触发器可以包含多个触发操作（多个 SQL 语句），可以为每个事件类型编写包含多个具体逻辑的复杂触发器。这样可以在一个触发器中处理多个逻辑，满足复杂的业务需求。

注意：

在设计和实施触发器时，应该谨慎考虑其数量和复杂性，以避免对数据库性能产生不必要的影响。过多或复杂的触发器可能会导致增加了额外的处理时间和资源消耗，降低数据库的整体性能。

触发器可以使用 Transact-SQL 编写，并在表创建或修改时使用 CREATE TRIGGER 语句进行定义。即触发器可以在数据库中使用CREATE TRIGGER语句定义，并通过ALTER TRIGGER语句进行修改。触发器可以在表级别或视图级别创建，具体取决于需要对其进行操作的对象。触发器的定义包括触发器的名称、关联表名、事件类型（INSERT、UPDATE或DELETE）、触发时间（BEFORE或AFTER）以及触发器中要执行的代码。

注意：

触发器可能会对数据库的性能产生影响，特别是当它们涉及到大量数据的时候。因此，在设计和使用触发器时，应该仔细考虑其对性能的潜在影响，并确保它们被正确地配置和处理。

二、触发器优缺点

触发器具有以下优点和缺点：

2.1优点：

  • 数据完整性：触发器可以用于强制执行数据完整性和业务规则，确保数据的有效性和一致性。
  • 自动化处理：触发器能够在特定的数据库操作发生时自动触发，无需手动干预，提高了开发效率和数据处理的准确性。
  • 日志记录和审计：通过在触发器中记录数据变更的信息，可以实现数据的日志记录和审计功能，有助于跟踪数据的变化历史和解决争议。
  • 灵活性：触发器可以根据业务需求进行定制和扩展，可以在数据变更时执行自定义的业务规则和逻辑。
  • 数据复制和同步：通过触发器，可以在数据发生变更时自动触发复制或同步操作，确保数据的一致性和可用性。

  • 安全控制：触发器可以用于实施安全控制策略，限制对敏感数据的访问和修改。通过在触发器中添加逻辑，可以根据用户权限和角色进行访问控制。

2.2缺点：

  • 性能影响：触发器的执行会增加数据库服务器的负载，可能导致性能下降。特别是复杂的触发器逻辑或在大量数据操作时，对性能的影响更为显著。
  • 难以调试：触发器的错误和异常处理可能比较困难，因为它们是在数据库操作时自动触发的，难以进行直接的调试。
  • 隐藏逻辑：触发器的逻辑是隐式的，可能会使代码维护和理解变得更加困难，特别是当有多个触发器存在时。
  • 数据一致性的挑战：触发器的复杂逻辑或错误的设计可能导致数据一致性的问题，需要仔细设计和测试。
  • 存储空间占用：每个触发器都占用存储空间，并且在每个相关事件发生时都会被加载和运行。如果存在多个触发器，将占用更多的存储空间。
  • 可能引发死锁和循环：触发器的执行可能导致死锁情况，尤其是在多个触发器相互依赖或嵌套调用的情况下。此外，触发器的不正确使用可能导致循环触发，进一步影响数据库性能和稳定性。

因此，在使用触发器时，需要仔细权衡其优点和缺点，并根据具体需求和性能要求合理设计和使用触发器。

三、触发器种类

SQL Server包括3种常规类型的触发器：DML触发器、DDL触发器和登录触发器。
当数据库中发生数据操作语言(DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的Transact-SQL语句。
用户可以设计以下类型的DML触发器。

  • ☑ AFTER触发器：在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。
  • ☑ INSTEAD OF触发器：执行INSTEAD OF触发器代替通常的触发动作。还可为带有一个或多个基表的视图定义INSTEAD OF触发器，而这些触发器能够扩展视图可支持的更新类型。
  • ☑ CLR触发器：CLR触发器可以是AFTER触发器或INSTEAD OF触发器。CLR触发器还可以是DDL触发器。CLR触发器将执行在托管代码（在.NET Framework中创建并在SQL Server中上载的程序集的成员)中编写的方法，而不用执行Transact–SQL存储过程。

DDL触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发，可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。

登录触发器将为响应LOGON事件而激发存储过程。与SQL Server实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。可以使用登录触
发器来审核和控制服务器会话，如通过跟踪登录活动、限制SQL Server的登录名或限制特定登录名的会话数。

四、触发器创建

创建DML触发器、DDL触发器和登录触发器可以通过执行CREATE TRIGGER语句实现。

4.1创建DML触发器

如果用户要通过数据操作语言(DML)事件编辑数据，则执行DML触发器。DML事件是针对表
或视图的NSERT、UPDATE或DELETE语句。
创建DML触发器的语法格式如下：

CREATE TRIGGER [schema_name.]trigger_name
ON {table|view}
[WITH [,...n ]
{FOR | AFTER | INSTEAD OF}
{[INSERT][,][UPDATE][,][DELETE]}
[WITH APPEND]
[NOT FOR REPLICATION]
AS {sql_statement [;][,...n ]| EXTERNAL NAME }
::=
   [ENCRYPTION]
   [EXECUTE AS Clause]
::=
  assembly_name.class_name.method name

对其进行解释： 

  • [schema_name.]trigger_name：指定触发器的名称，可以选择性地包含架构名称。trigger name必须遵循标识符规则，但trigger_name不能以“#”或“##”开头.
  • ON {table|view}：指定触发器关联的表或视图。视图只能被INSTEAD OF触发器引用。不能对局部或全局临时表定义DML触发器。
  • [WITH [,…n ]]：可选项，用于指定额外的触发器选项。例如，可以使用ENCRYPTION选项以加密触发器的定义，或使用EXECUTE AS Clause选项以指定触发器执行的安全上下文。
  • {FOR | AFTER | INSTEAD OF}：指定触发器执行的时间点。FOR表示在操作之前执行，AFTER表示在操作之后执行，INSTEAD OF表示替代原始操作执行。【AFTER指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发；INSTEAD OF是指定执行DML触发器而不是触发SQL语句，因此，其优先级高于触发语句的操作。】
  • {[INSERT][,][UPDATE][,][DELETE]}：指定触发器要响应的事件类型。可以选择INSERT、UPDATE和DELETE关键字来指定触发器响应的操作类型。它们指定数据修改语句，这些语句可在DML触发器对此表或视图进行尝试时激活该触发器。但必须至少指定一个选项。
  • [WITH APPEND]：可选项，用于在已有的触发器代码后附加新的代码。指定应该再添加一个现有类型的触发器。
  • [NOT FOR REPLICATION]：可选项，指定触发器不会用于数据库复制过程中的操作。即指示当复制代理修改涉及触发器的表时，不应执行触发器。
  • AS {sql_statement [;][,…n ]| EXTERNAL NAME }：触发器的代码块位于AS关键字之后。可以编写T-SQL语句和语法来实现所需的功能，也可以使用EXTERNAL NAME来引用托管代码的方法。 
  • EXECUTE AS：指定用于执行该触发器的安全上下文。
  • ：对于CLR触发器，指定程序集与触发器绑定的方法。该方法不能带有任何参数，并且必须返回空值。

通常需求都是很普通的，命令按照如下格式即可：

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
[AFTER/INSTEAD OF] {INSERT, UPDATE, DELETE}
AS
BEGIN
    -- 触发器逻辑代码
END

以下是DML触发器的示例：

  • 例一：提前创建测试表：Employees和相关表-日志表：SalaryLog，然后创建触发器并验证，详细命令如下：
use SJCX ;  --选用数据库
  --创建测试表Employees
  CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10, 2)
);
  
  --插入数据
  INSERT INTO Employees (ID, Name, Salary)
VALUES
    (1, 'John Doe', 5000),
    (2, 'Jane Smith', 6000),
    (3, 'Mike Johnson', 4500);
    
 --创建相关表-日志表：SalaryLog
 CREATE TABLE SalaryLog (
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    ChangeDate DATETIME
);

--检查表数据
select * from Employees;
select * from SalaryLog;


--创建触发器：
CREATE TRIGGER UpdateSalaryTrigger
ON Employees
FOR UPDATE
AS
BEGIN
    -- 如果薪水列被修改，则记录一条日志
    IF UPDATE(Salary)
    BEGIN
        INSERT INTO SalaryLog (EmployeeID, OldSalary, NewSalary, ChangeDate)
        SELECT d.ID, d.Salary, i.Salary, GETDATE()
        FROM inserted AS i
        INNER JOIN deleted AS d ON i.ID = d.ID;
    END
END;

--触发器验证：
--更新"Employees"表中员工ID为1的记录的薪水：
UPDATE Employees
SET Salary = 5500
WHERE ID = 1;

--结果查验：
--使用SELECT语句来检查"SalaryLog"表中的日志记录是否生成：
select * from SalaryLog;

在触发器中，我使用了内置的UPDATE()函数来检查是否修改了”Salary”列。如果是，则将相应的旧薪水、新薪水和修改日期插入到名为”SalaryLog”的日志表中。然后我 更新”Employees”表中的1号员工的薪水，并检查”SalaryLog”表中是否出现了对应的日志记录。日志记录运行结果如下：

  • 例二：提前创建”Customers”表和”CustomerLog”表 ，然后创建触发器并验证，详细命令如下：
--示例2：
--创建"Customers"表
CREATE TABLE dbo.Customers (
    CustomerId INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Address VARCHAR(200)
);


--创建"CustomerLog"表：
CREATE TABLE dbo.CustomerLog (
    LogId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT,
    Action VARCHAR(10),
    LogDate DATETIME
);

--数据检查：
SELECT * FROM Customers;
SELECT * FROM CustomerLog;



-- 创建触发器
CREATE TRIGGER dbo.LogInserts
ON dbo.Customers
AFTER INSERT
AS
BEGIN
    INSERT INTO dbo.CustomerLog (CustomerId, Action, LogDate)
    SELECT CustomerId, 'INSERT', GETDATE()
    FROM inserted;
END

--触发器验证：
--插入数据：
INSERT INTO dbo.Customers (CustomerId, CustomerName, Address)
VALUES (1, 'John Doe', '123 Main St'),
       (2, 'Jane Smith', '456 Oak Ave');

--结果检查：
--数据检查：
SELECT * FROM Customers;
SELECT * FROM CustomerLog;

该触发器解释： 

  • 触发器名字为”dbo.LogInserts”，位于”default”架构下。
  • 触发器将在”Customers”表中的INSERT操作之后执行。
  • 在触发器代码块中，我向”CustomerLog”表中插入新行，包括插入的”CustomerId”、操作类型为’INSERT’以及当前的日期和时间。

 为了测试这个触发器，我是按照以下步骤进行：

  1. 提前要确保数据库中存在”Customers”表和”CustomerLog”表，并且它们具有正确的结构。
  2. 执行上述CREATE TRIGGER语句以创建触发器。
  3. 向”Customers”表中插入一些新行。
  4. 检查”CustomerLog”表是否包含与插入操作相对应的日志行。

 日志记录运行结果如下：

  • 例三：DELETE类型的DML触发器， 详细命令如下：
--示例3：
--借用上面的测试表Customers：

--创建相关表DeletedCustomersLog：
CREATE TABLE DeletedCustomersLog (
    ID INT,
    Name VARCHAR(50),
    Address VARCHAR(100),
    DeleteDate DATETIME
);

--表检查：
SELECT * FROM Customers;
SELECT * FROM DeletedCustomersLog;


-- 创建触发器
CREATE TRIGGER DeleteCustomerTrigger
ON Customers
AFTER DELETE
AS
BEGIN
    -- 记录被删除的客户信息到日志表
    INSERT INTO DeletedCustomersLog (ID, Name, Address, DeleteDate)
    SELECT CustomerID, CustomerName, Address, GETDATE()
    FROM deleted;
END;

--触发器验证：
--尝试从"Customers"表中删除某个客户，并检查触发器是否被触发并在"DeletedCustomersLog"表中生成了相应的日志记录：
DELETE FROM Customers
WHERE CustomerID = 2;

--结果检查：
SELECT * FROM Customers;
SELECT * FROM DeletedCustomersLog;

  日志记录运行结果如下：

4.2创建DDL触发器

在SQL Server 2008中，DDL触发器（Data Definition Language triggers）是用于响应数据库的结构更改操作（如创建、修改或删除表、视图、存储过程等）的触发器。DDL触发器在执行某个DDL语句之前或之后触发，并且可以被用于验证、记录或阻止这些结构更改。

DDL触发器可以根据不同的事件和条件进行触发。以下是一些DDL触发器的执行条件：

  • 1. AFTER 触发点: DDL触发器可以在相应DDL语句执行之后触发。
  • 2. INSTEAD OF 触发点: DDL触发器可以替代相应DDL语句的执行。
  • 3. CREATE、ALTER或DROP 事件: DDL触发器可以绑定到特定的CREATE、ALTER或DROP事件上。
  • 4. 特定对象类型: DDL触发器可以只针对特定类型的对象（如表、视图、存储过程等）进行触发。
  • 5. 条件表达式: DDL触发器可以使用条件表达式来判断是否触发，例如基于某个特定的DDL语句文本或其他相关的条件。

创建DDL触发器的语法格式如下：

CREATE TRIGGER trigger_name
ON {ALL SERVER | DATABASE}
[WITH [,...n ]]
{FOR | AFTER}{event_type | event_group }[,...n]
AS {sql_statement [;][,...n]| EXTERNAL NAME  [;]}
::=
  [ENCRYPTION]
  [EXECUTE AS Clause]
::=
   assembly_name.class_name.method name

对其进行解释：

  • – `CREATE TRIGGER trigger_name`: 使用`CREATE TRIGGER`语句创建一个触发器，为触发器指定一个独一无二的名称来替换`trigger_name`。
  • – `ON {ALL SERVER | DATABASE}`: 指定触发器绑定到全部服务器级别事件（ALL SERVER）或当前数据库级别事件（DATABASE）。
  • – `[WITH [,…n ]]`: 可选项，用于指定触发器的属性和选项。常见的选项有`ENCRYPTION`用于加密触发器代码，以及`EXECUTE AS Clause`用于指定在触发器执行时使用的安全上下文。
  • – `{FOR | AFTER}{event_type | event_group }[,…n]`: 指定触发器应该响应的事件类型或事件组。可以选择使用`FOR`或`AFTER`关键字，后跟一个或多个事件类型或事件组。例如，可以指定`FOR CREATE_TABLE`表示在创建新表之前触发触发器。
  • – `AS {sql_statement [;][,…n]| EXTERNAL NAME [;]}`: 触发器的主体部分。可以是一组SQL语句，以`;`分隔，用于在触发事件发生时执行特定操作。另外也可以使用`EXTERNAL NAME`关键字指定触发器连接到托管代码的方法。

以下是DDL触发器的示例：

创建一个触发器，当对库中表进行创建、修改或删除的操作时，向日志表插入记录，详细命令如下：

 --创建 TestTable 的测试表：
 -- 创建测试表
CREATE TABLE TestTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);


--创建LogTable 的日志表:
--用于记录触发器操作
-- 创建日志表
CREATE TABLE LogTable (
    EventDateTime DATETIME,
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(100)
);

--表检查
SELECT * FROM TestTable;
SELECT * FROM LogTable;
 
 --创建触发器：
CREATE TRIGGER MyDDLTrigger
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    -- 在这里编写触发器的逻辑

    -- 示例：在触发器被触发时，向日志表插入一条记录
    INSERT INTO dbo.LogTable (EventDateTime, EventType, ObjectName)
    VALUES (
            GETDATE(), 
            EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'), 
            EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')
            )
END;

--备注：该触发器的示例逻辑会在任何创建、修改或删除表的操作发生时向一个名为 dbo.LogTable 的日志表插入一条记录

--测试触发器
--尝试执行一些包括创建、修改或删除表的操作
-- 创建新表
CREATE TABLE NewTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

-- 修改测试表
ALTER TABLE TestTable ADD Age INT;

-- 删除表
DROP TABLE NewTable;


--数据检查
--完成这些操作后，查询日志表 LogTable 来查看触发器的执行情况：
SELECT * FROM LogTable;

通过上述触发器，当任何创建、修改或删除表的操作发生时，将会向 dbo.LogTable 日志表中插入一条记录，记录了触发事件的日期时间、事件类型和对象名称。日志记录运行结果如下： 

4.3创建登录触发器

登录触发器在遇到LOGON事件时触发。LOGON事件是在建立用户会话时引发的。触发器可以由
Transact–SQL语句直接创建，也可以由程序集方法创建，这些方法是在Microsoft.NET Framework公共语言运行时(CLR)中创建并上载到SQL Server实例的。SQL Server允许为任何特定语句创建多个触发器。
创建登录触发器的语法格式如下：

CREATE TRIGGER trigger_name
ON ALL SERVER
[WITH [,..n]]
{FOR | AFTER} LOGON
AS {sql_statement [;][,...n ]|EXTERNAL NAME [;]}
::=
   [ENCRYPTION]
   [EXECUTE AS Clause]
::=
  assembly_name.class_name.method_name

该语法是用于创建一个在所有服务器上的触发器，以下是解释：

  • trigger_name： 是为触发器指定的名称，可以根据需求进行命名。
  • ON ALL SERVER：表示触发器将应用于所有连接到服务器的用户。
  • [WITH [,..n]]： 是可选参数，用于定义触发器的选项。其中包括 ENCRYPTION 和 EXECUTE AS Clause。
  • {FOR | AFTER} LOGON：FOR 或 AFTER 关键字用于指定触发器是在用户登录之前还是之后执行。
  • AS {sql_statement [;][,…n ]|EXTERNAL NAME [;]}：在这里，可以指定触发器的主体部分。它可以是一个或多个 SQL 语句，以及可选的外部方法规范符。
  • ::=   assembly_name.class_name.method_name：如果选择使用外部方法，method_specifier 将指定程序集名称、类名称和方法名称。外部方法允许您调用托管代码来执行触发器逻辑。

示例1：创建一个登录触发器，该触发器拒绝TM登录名的成员登录SQL Server。详细命令如下：

USE master;--将当前会话的默认数据库设置为 master
GO
--创建一个名为 TM 的登录账号，并设置密码为 'TMsoft'。
--MUST_CHANGE 参数表示用户首次登录时必须更改密码，CHECK_EXPIRATION 参数表示密码过期策略启用
CREATE LOGIN TM WITH PASSWORD ='TMsoft'MUST_CHANGE,
  CHECK_EXPIRATION=ON;
GO
--授予登录账号 TM 对服务器状态信息 (sys.dm_exec_sessions) 的查看权限
GRANT VIEW SERVER STATE TO TM;
GO
--创建一个名为 connection_limit_trigger 的登录触发器，并将其绑定到服务器级别的 LOGON 事件上。
--触发器使用 EXECUTE AS 'TM' 语句指定以登录账号 TM 的身份执行触发器的逻辑。
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER 
 WITH EXECUTE AS 'TM'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()='TM'AND
    (SELECT COUNT(*)FROM sys.dm_exec_sessions
           WHERE is_user_process=1 AND
             original_login_name='TM')>1  
 --判断正在登录的用户是否为 TM，然后通过查询 sys.dm_exec_sessions 视图统计当前活跃的会话数量。
 --如果会话数大于1，则执行回滚操作，阻止用户的登录。
    ROLLBACK;
END;

登录触发器与DML触发器、DDL触发器所存储的位置不同，其存储位置为对象资源管理器中“服
务器对象”/“触发器”。登录触发器connection_limit_trigger中的TM为登录到SQL Server中的登录名。触发器及TM所在的位置如图所示。

创建完该触发器后，当以TM的登录名登录SQL Server时，就会显示如下图所示的提示信息。

 示例2：创建登录触发器，禁止名为RestrictedUser的特定用户登录，详细命令如下：

--创建登录触发器：禁止名为RestrictedUser的特定用户登录
CREATE TRIGGER LoginTrigger
ON ALL SERVER   -- 触发器将在服务器级别上执行，而不是特定的数据库
FOR LOGON       --触发器将在用户登录到服务器时触发
AS
BEGIN
    -- 在此处编写触发器逻辑

    -- 示例：禁止特定用户登录
    IF ORIGINAL_LOGIN() = 'RestrictedUser'
    BEGIN
        ROLLBACK;
        RAISERROR ('Access Denied', 16, 1);
        RETURN;
    END
END

解析逻辑：

如果触发器检测到原始登录名 (ORIGINAL_LOGIN()) 等于 ‘RestrictedUser’，它会执行以下操作：  

  • –ROLLBACK: 回滚当前的登录尝试，阻止用户登录。
  • –RAISERROR: 抛出一个错误消息 ‘Access Denied’，提示访问被拒绝。
  • –RETURN: 结束触发器的执行。  
  • –RAISERROR (‘Access Denied’, 16, 1) 是用于抛出错误消息的语句。
  • –‘Access Denied’ 是指要抛出的错误消息的文本内容，表示拒绝访问。
  • –16 是指错误的严重级别。在 SQL Server 中，错误级别从 1 到 25 不等，其中级别越高表示错误的严重程度越高。级别 16 表示一般的用户定义错误。
  • –1 是指状态值，表示此错误的自定义状态值。  

注意：

 RAISERROR 已在 SQL Server 2012 及更高版本中被替代为 THROW 语句。因此，在较新的版本中，可以使用 THROW 语句来实现相同的效果。

五、触发器管理

触发器的查看、修改、重命名与删除等操作都可以使用SQL Server Management Studio管理工具实现。下面会通过SQL Server Management Studio管理工具来查看、修改、重命名、禁用和启用与删除触发器。

5.1查看触发器

查看触发器与查看存储过程相同。同样可以使用sp_helptext存储过程与sys.sql_modules视图查看触发器。

5.1.1.使用sp_helptext存储过程查看触发器

sp_helptext存储过程可以查看架构范围内的触发器，非架构范围内的触发器是不能用此存储过程查看的，如DDL触发器、登录触发器。

语法格式如下：

USE YourDatabaseName;
EXEC sp_helptext 'YourTriggerName';

将 YourDatabaseName 替换为要查询触发器所在的数据库名，YourTriggerName 替换为要查看的触发器名称。 单击 “执行” 按钮或按下 F5 键来运行查询。查询结果窗口将显示与指定触发器关联的 T-SQL 代码。

例如sp_helptext存储过程查看上述例子中的DML触发器–【UpdateSalaryTrigger】。SQL语句及运行结果如图所示：

注意：

sp_helptext 存储过程返回与对象关联的所有文本，包括触发器的完整定义和逻辑代码。如果触发器非常复杂或代码较长，结果可能会被截断或超出显示范围。在这种情况下，您可以使用 SSMS 的结果网格选项卡或将结果导出到文件以查看完整的代码。 

5.1.2.获取数据库中触发器的信息

每个类型为TR或TA的触发器对象对应一行，TA代表程序集(CLR)触发器，TR代表SQL触发器。DML触发器名称在架构范围内，因此，可在sys.objects中显示。DDL触发器名称的作用域取决于父实体，只能在对象目录视图中显示。
例如在db2008数据库中，查找类型为TR的触发器。SQL语句及运行结果如图所示。

sys.objects 视图包含了数据库中所有类型的对象（如表、视图、函数、存储过程等），通过 WHERE TYPE='TR' 过滤条件可以只返回触发器类型的对象。虽然触发器也是数据库中的对象，但由于其特殊性质，SQL Server 将其单独列为一个视图来管理触发器相关的元数据信息。因此，使用 sys.triggers 视图能够更准确地检索与触发器相关的信息。

如果希望查询数据库中所有触发器的详细信息，建议使用以下查询：

SELECT *
FROM sys.triggers;

结果如下：

这里将返回指定数据库中所有触发器的详细信息，包括触发器名称、关联对象、事件类型等。

sys.triggers 视图：专门用于检索与触发器相关的信息。它包含数据库中所有触发器对象的元数据信息，例如触发器名称、所属表名、事件类型等。

在 SQL Server 2008 中，DDL（数据定义语言）触发器无法直接查看其定义和逻辑代码。这是因为 SQL Server 2008 不提供一种简单的方法来检索 DDL 触发器的完整定义。然而，可以通过查询系统表来获取有关数据库中的 DDL 触发器的一些基本信息。 打开 SQL Server Management Studio (SSMS) 工具并连接到数据库实例，在 “新建查询” 窗口中，输入以下命令：

USE YourDatabaseName;
SELECT * 
FROM sys.triggers 
WHERE [type] = 'TR' AND parent_class_desc = 'DATABASE';

将 YourDatabaseName 替换为要查询触发器所在的数据库名。

例如想查找我上面所创建的DDL触发器， SQL运行结果如图所示：

注意：

此查询将返回与数据库关联的所有 DDL 触发器的基本信息，但不会包含触发器的完整定义和逻辑代码。如果需要查看特定 DDL 触发器的详细定义和逻辑，请尝试使用其他工具、脚本或第三方插件，如 Red Gate SQL Prompt 或 ApexSQL Refactor，这些工具可以帮助我们更方便地查看和管理 DDL 触发器。 

DDL（数据定义语言）触发器和 DML（数据操作语言）触发器是两种不同类型的触发器：

  • DDL 触发器（Data Definition Language Triggers）：这些触发器对于数据库中的结构性更改事件（例如创建、修改或删除表、视图、存储过程等）进行响应。
  • DML 触发器（Data Manipulation Language Triggers）：这些触发器对于针对表的数据操作事件（例如插入、更新或删除行）进行响应。

通过 SELECT * FROM sys.objects WHERE TYPE='TR' 查询，将获得包含所有类型的触发器的结果，无论是 DDL 触发器还是 DML 触发器。但是如果想进一步区分 DDL 触发器和 DML 触发器，可以参考以下查询示例：

SELECT *
FROM sys.triggers
WHERE parent_class = 0; -- DDL 触发器

SELECT *
FROM sys.triggers
WHERE parent_class > 0; -- DML 触发器

运行结果如图所示：

 从结果图可观察到上述已经创建的一些触发器。发现上述查询可以根据 parent_class 列的值来区分 DDL 触发器和 DML 触发器。 

除了上述的方法查看触发器以外，也可以通过下面的一些方法进行查看：

在 SQL Server 2008 中，查看数据库中的 DML（数据操作语言）触发器：

  • 1. 打开 SQL Server Management Studio (SSMS) 工具并连接到您的数据库实例。
  • 2. 在 “对象资源管理器” 中，展开数据库文件夹，然后展开 “表” 文件夹。
  •    
  • 3. 找到包含 DML 触发器的表，并展开该表。
  • 4. 在展开的表节点下，找到 “触发器” 文件夹。在该文件夹中，您将看到与该表关联的触发器列表。
  • 5. 右键单击所需的触发器，然后选择 “脚本触发器为” > “创建到” > “新查询编辑器窗口” 或 “剪贴板”。
  •    – “创建到新查询编辑器窗口”：将生成触发器的 T-SQL 脚本，并在新的查询编辑器窗口中打开以进行查看和编辑。
  •    – “剪贴板”：将生成触发器的 T-SQL 脚本，并复制到剪贴板中供您粘贴到其他位置。

通过上述步骤，可以查看数据库中表的 DML 触发器及其相关的 T-SQL 脚本。这样就可以了解触发器的逻辑和操作，以满足我们的需求。

注意：

这些步骤假设我们已经拥有足够的权限来查看触发器对象。如果遇到任何问题，要确保使用具有适当权限的登录账号连接到 SQL Server 实例。

5.2修改触发器

修改触发器可以通过ALTER TRIGGER语句实现，下面分别对修改DML触发器、DDL触发器、
登录触发器进行介绍。

5.2.1修改DML触发器

修改DML触发器的语法格式如下：

ALTER TRIGGER trigger_name
ON table_name
AFTER/INSTEAD OF {INSERT, UPDATE, DELETE}
AS
BEGIN
    -- 触发器逻辑代码
END;

注意几点： 

  • ALTER TRIGGER 用于修改现有的触发器。
  • trigger_name 是要修改的触发器的名称。
  • table_name 是触发器所属的表的名称。
  • AFTER 或 INSTEAD OF 用于指定触发器在何时触发。AFTER 表示在执行完相应的 DML 操作后触发，而 INSTEAD OF 则表示在代替相应的 DML 操作之前触发。
  • {INSERT, UPDATE, DELETE} 是触发器要响应的操作类型。可以选择 INSERT（插入）、UPDATE（更新）或 DELETE（删除），也可以同时选择多个操作类型。
  • AS BEGIN ... END 是触发器的主体部分，其中包含触发器的逻辑代码。

例如使用ALTER TRIGGER语句修改DML触发器DeleteCustomerTrigger,当向该表中插入、修改或删除数据时给出提示信息。SQL语句如下：

--修改DML触发器：DeleteCustomerTrigger
ALTER TRIGGER DeleteCustomerTrigger
ON Customers
AFTER INSERT, UPDATE, DELETE
AS
RAISERROR('正在向表中插入、修改或删除数据',16,10);

验证修改的触发器，向表中插入数据，结果如下所示：

表结果检查，如图所示：

5.2.2修改DDL触发器

修改DML触发器的语法格式如下：

ALTER TRIGGER trigger_name
ON DATABASE
FOR {EVENT_TYPE}
AS
BEGIN
    -- 触发器逻辑代码
END;
  • ALTER TRIGGER 用于修改现有的触发器。
  • trigger_name 是要修改的 DDL 触发器的名称。
  • FOR {EVENT_TYPE} 用于指定触发器要响应的事件类型，可以是 CREATE_TABLE、ALTER_TABLE 或 DROP_TABLE 等。
  • BEGIN ... END 是触发器的主体部分，其中包含触发器的逻辑代码。 

例如使用ALTER TRIGGER语句修改DDL触发器MyDDLTrigger,防止用户修改数据。SQL语句如下：

CREATE TRIGGER MyDDLTrigger
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    RAISERROR('只有“MyDDLTrigger”触发器无效时，才可以修改表。', 16, 10);
    ROLLBACK;
END;

验证已修改完成的触发器，对表进行alter table 操作，结果如图所示：

5.2.3修改登录触发器

修改登录触发器的语法格式如下：

ALTER TRIGGER trigger_name
ON ALL SERVER
FOR LOGON
AS
BEGIN
    -- 触发器逻辑代码
END;

注意几点：

  • ALTER TRIGGER 用于修改现有的触发器。
  • trigger_name 是要修改的登录触发器的名称。
  • ON ALL SERVER 表示该触发器将应用于所有服务器级别的事件。
  • FOR LOGON 指定触发器要响应的事件类型为用户登录事件。
  • BEGIN ... END 是触发器的主体部分，其中包含触发器的逻辑代码。

在编写登录触发器时，通常会使用一些系统函数（如 ORIGINAL_LOGIN() 和 HOST_NAME() 等）来获取相关信息，以便进行相应的处理。

例如使用ALTER TRIGGER语句修改登录触发器connection_limit_trigger,将用户名修改为nxt,如果在此登录名下已运行3个用户会话，拒绝nxt登录到SQL Server。SQL语句如下：

--修改登录触发器：
ALTER TRIGGER connection_limit_trigger
ON ALL SERVER
WITH EXECUTE AS'NXT'
FOR LOGON
AS
BEGIN
    -- 触发器逻辑代码
    IF ORIGINAL_LOGIN ()='NXT' AND
    (SELECT COUNT(*) FROM SYS.dm_exec_sessions 
       WHERE is_user_process =1 AND
         original_login_name ='NXT')>3
     ROLLBACK;
END;

验证修改的登录触发器，使用用户名 “NXT” 进行登录尝试，并确保同时存在超过3个与该用户名相关的活动会话。

首先修改TM用户名为NXT，命名如下：

--修改用户名：
ALTER LOGIN TM
WITH NAME = NXT;

用户名为 “NXT” 并且与该用户名相关的活动会话数超过3个，则触发器将触发 ROLLBACK 语句，撤销登录尝试，并提供一个错误消息。如图所示：

5.3重命名触发器

重命名触发器可以使用sp_rename系统存储过程实现。使用sp_rename系统存储过程重命名触发器
与重命名存储过程相同。但是使用该系统存储过程重命名触发器，不会更改sys.sql modules类别视图的definition(用于定义此模块的SQL文本)列中相应对象名的名称，所以建议用户不要使用该系统存储过程重命名触发器，而是删除该触发器，然后使用新名称重新创建该触发器。

可以使用以下语法格式来重命名触发器：

sp_rename 'old_trigger_name', 'new_trigger_name', 'OBJECT';

old_trigger_name 是要重命名的现有触发器的名称，new_trigger_name 是要修改为的新触发器名称。

注意几点：

  • sp_rename 是一个系统存储过程，用于重命名数据库对象。
  • 'OBJECT' 参数表示要重命名的对象是一个触发器。
  • 重命名触发器的操作需要具有适当的权限。

 例如：使用sp_rename将触发器DeleteCustomerTrigger重命名为DeleteCusTrigger。SQL语句如下：

--重命名触发器：
sp_rename 'DeleteCustomerTrigger','DeleteCusTrigger'

检查修改后的触发器，如图所示：

5.4禁用与启用触发器

当不再需要某个触发器时，可将其禁用或删除。禁用触发器不会删除该触发器，该触发器仍然作为对象存在于当前数据库中。但是，当执行任意NSERT、UPDATE或DELETE语句（在其上对触发器进行了编程)时，触发器将不会激发。已禁用的触发器可以被重新启用，启用触发器会以最初创建它时的方式将其激发。默认情况下，创建触发器后会启用触发器。

5.4.1禁用触发器

使用以下语法格式来禁用触发器：

DISABLE TRIGGER {trigger_name | ALL}
ON {table_name | view_name}
  • trigger_name：要禁用的单个触发器名称。
  • ALL：禁用指定表或视图上的所有触发器。
  • table_name：要禁用触发器的表名。
  • view_name：要禁用触发器的视图名。

 注意几点：

  • 禁用触发器需要具有适当的权限。
  • 当禁用触发器后，相关的 DML 操作将不会触发该触发器的执行。
  • 被禁用的触发器仍然存在于数据库中，并可以在以后重新启用。

例1：使用DISABLE TRIGGER语句禁用DML触发器DeleteCusTrigger，SQL语句如下：

--禁用DML触发器：
DISABLE TRIGGER DeleteCusTrigger
ON CUSTOMERS;

例2：使用DISABLE TRIGGER语句禁用DDL触发器MyDDLTrigger，SQL语句如下：

--禁用DDL触发器：
DISABLE TRIGGER MyDDLTrigger
ON DATABASE;

例3：使用DISABLE TRIGGER语句禁用登录触发器connection_limit_trigger，SQL语句如下：

--禁用登录触发器：
DISABLE TRIGGER connection_limit_trigger
ON ALL SERVER;

检查已经禁用的各个触发器的状态，SQL语句如下：

--查看触发器状态：0-启动；1-禁用
SELECT 
    name AS trigger_name,
    is_disabled
FROM 
    sys.triggers
WHERE 
    is_disabled = 1;

--查看登录触发器的状态
SELECT 
    name AS trigger_name,
    is_disabled
FROM 
    sys.server_triggers
WHERE 
    is_disabled = 1;

 执行结果如图所示：

5.4.2启用触发器

启用触发器并不是重新创建它。已禁用的DDL、DML或登录触发器可以通过执行ENABLE TRIGGER语句重新启用。语法格式如下： 

ENABLE TRIGGER {trigger_name | ALL}
ON {table_name | view_name}
  • trigger_name：要启用的单个触发器名称。
  • ALL：启用指定表或视图上的所有触发器。
  • table_name：要启用触发器的表名。
  • view_name：要启用触发器的视图名。

注意几点：

  • 启用触发器需要具有适当的权限。
  • 当启用触发器后，相关的 DML 操作将会触发该触发器的执行。
  • 如果触发器已经是启用状态，则启用触发器操作将不会产生任何影响。

例1：使用ENABLE TRIGGER语句启用DML触发器DeleteCusTrigger，SQL语句如下：

--启用DML触发器：
ENABLE TRIGGER DeleteCusTrigger
ON CUSTOMERS;

例2：使用ENABLE TRIGGER语句启用DDL触发器MyDDLTrigger，SQL语句如下：

--启用DDL触发器：
ENABLE TRIGGER MyDDLTrigger
ON DATABASE;

例3：使用ENABLE TRIGGER语句启用登录触发器connection_limit_trigger，SQL语句如下：

--启用登录触发器：
ENABLE TRIGGER connection_limit_trigger
ON ALL SERVER;

检查已经禁用的各个触发器的状态，结果如下所示：

5.5删除触发器

删除触发器是将触发器对象从当前数据库中永久地删除。通过执行DROP TRIGGER语句可以将
DML、DDL或登录触发器删除，也可以通过操作SQL Server Management Studio手动删除DML、DDL或登录触发器。

5.5.1.DROP TRIGGER语句删除触发器

DROP TRIGGER语句可以从当前数据库中删除一个或多个DML、DDL或登录触发器。
(1)删除DML触发器
删除DML触发器的语法格式如下：

DROP TRIGGER [IF EXISTS] {trigger_name | table_name.trigger_name}
  • IF EXISTS（可选）：用于在触发器不存在时避免报错。
  • trigger_name：要删除的单个触发器名称。
  • table_name.trigger_name：要删除的表上的指定触发器名称。

注意几点：

  • 删除触发器需要具有适当的权限。
  • 当删除触发器后，与该触发器相关的 DML 操作将不再触发该触发器。
  • 删除触发器时要小心，确保没有意外删除了需要的触发器。

例如使用DROP TRIGGER语句禁用DML触发器DeleteCusTrigger，SQL语句如下：

--删除DML触发器
DROP TRIGGER DeleteCusTrigger

(2)删除DDL触发器
删除DDL触发器的语法格式如下：

DROP TRIGGER [IF EXISTS] {trigger_name | ddl_trigger_name}
ON {database_name | ALL SERVER}
  • IF EXISTS（可选）：用于在触发器不存在时避免报错。
  • trigger_name：要删除的单个触发器名称。
  • ddl_trigger_name：要删除的 DDL 触发器的名称。
  • database_name：指定触发器所在的数据库名称。
  • ALL SERVER：删除所有服务器级别的触发器。

注意以下几点：

  • 删除触发器需要具有适当的权限。
  • 当删除触发器后，与该触发器相关的 DDL 操作将不再触发该触发器。
  • 删除触发器时要小心，确保没有意外删除了需要的触发器。

例如使用DROP TRIGGER语句禁用DML触发器MyDDLTrigger，SQL语句如下：

----删除DDL触发器
DROP TRIGGER MyDDLTrigger
ON DATABASE;

(3)删除登录触发器
删除登录触发器的语法格式如下： 

DROP TRIGGER [IF EXISTS] {trigger_name | logon_trigger_name}
ON ALL SERVER
  • IF EXISTS（可选）：用于在触发器不存在时避免报错。
  • trigger_name：要删除的单个触发器名称。
  • logon_trigger_name：要删除的登录触发器的名称。

注意几点：

  • 删除触发器需要具有适当的权限。
  • 当删除触发器后，与该触发器相关的登录事件将不再触发该触发器。
  • 删除触发器时要小心，确保没有意外删除了需要的触发器。

例如使用DROP TRIGGER语句禁用DML触发器connection_limit_trigger，SQL语句如下：

--删除登录触发器
DROP TRIGGER connection_limit_trigger
ON ALL SERVER;

提示：

一旦触发器被删除，相关的元数据信息将不再存在于系统视图中。因此，无法通过查询系统视图来确定触发器是否已经被删除。如果需要确认触发器是否已被删除，可以尝试执行与该触发器相关的操作（如插入、更新或删除数据），并观察是否会产生预期的触发器行为。如果触发器不存在，相关的触发器逻辑将不会被触发。

一定要注意，在删除触发器之前，最好备份数据库，以便在需要时进行恢复。

5.5.2.SQL Server Management Studio手动删除触发器

手动删除触发器的步骤如下：
(1)打开SQL Server Management Studio,并连接到SQL Server2008中的数据库。

(2)展开“对象资源管理器”中触发器所在位置。如要删除创建在db2008数据库的触发器，则展开如图所示的树形结构。

(3)右击要删除的触发器，在弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框，
如图所示。 

(4)在“删除对象”对话框中确认所删除的触发器，单击“确定”按钮即可将该触发器删除。 

六、触发器应用场景

结合前面内容的描述，现总结下触发器的应用场景，以下是一些常见的应用场景：

  • 1. 数据完整性约束：触发器可以用于实施数据完整性约束，确保特定的业务规则得到满足。例如，在插入或更新操作之前，可以使用触发器验证数据的有效性，并拒绝不符合要求的更改。
  • 2. 日志记录：触发器可以用于记录数据库中的数据变化。通过在触发器中编写代码，可以将所做的更改记录到一个日志表中，以便后续分析和审计。这对于跟踪数据修改、审计数据库操作等方面非常有用。
  • 3. 数据转换和派生：触发器可以用于根据已有数据生成新的派生数据。例如，当插入一条销售记录时，触发器可以根据该记录的某些属性计算并填充相关的报告字段；在数据插入或更新操作之前，可以使用触发器对数据进行格式化、计算或转换，以满足特定的需求。
  • 4. 数据复制：触发器可以用于在多个数据库之间复制数据。当在源数据库中插入、更新或删除记录时，触发器可以自动同步这些更改到目标数据库中的相应表。这对于将数据从一个表或数据库复制到另一个表或数据库非常有用，确保数据的一致性和可用性。
  • 5. 安全控制：触发器可以用于实施安全控制策略。例如，在敏感数据表上创建触发器，以防止未经授权的访问或修改数据。
  • 6. 异步处理：触发器可以用于在后台异步处理相关任务。例如，在处理用户请求时，触发器可以启动一个后台作业，以避免对用户的响应时间产生延迟。
  • 7.业务规则和逻辑：触发器允许在数据变更时执行自定义的业务规则和逻辑。例如，可以根据特定条件自动更新其他表中的数据、生成自定义报表或触发其他业务流程。
  • 8.引用完整性：触发器可以用于实施引用完整性，确保表之间的关联关系得到维护。通过触发器，可以在更新或删除关联表中的数据时强制执行相关的操作或防止破坏关联关系。

本篇关于触发器的总结就到此结束了，内容如有错误的地方还请兄弟们指点，如果想了解更多触发器相关的知识可以去看下我的另一篇文章，关于Oracle的触发器总结：http://t.csdnimg.cn/lDeaz

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: