ToB企服应用市场:ToB评测及商务社交产业平台

标题: SQL sever中的触发器 [打印本页]
作者: 反转基因福娃    时间: 2024-6-14 22:15
标题: SQL sever中的触发器
目录

一、触发器概念
二、触发器优缺点
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 支持多个触发器范例，包罗：

在SQL Server中一张表可以有多个触发器，每个表最多可以有一个触发器与每种触发事件相关联。也就是对于每个表，最多可以界说三个触发器，分别是一个插入触发器、一个更新触发器和一个删除触发器：
用户可以使用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触发器。

DDL触发器是一种特殊的触发器，它在响应数据界说语言(DDL)语句时触发，可以用于在数据库中实行管理任务，例如，审核以及规范数据库利用。
登录触发器将为响应LOGON事件而引发存储过程。与SQL Server实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话现实建立之前引发。可以使用登录触
发器来审核和控制服务器会话，如通过跟踪登录运动、限制SQL Server的登录名或限制特定登录名的会话数。
四、触发器创建

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

假如用户要通过数据利用语言(DML)事件编辑数据，则实行DML触发器。DML事件是针对表
或视图的NSERT、UPDATE或DELETE语句。
创建DML触发器的语法格式如下：
  1. CREATE TRIGGER [schema_name.]trigger_name
  2. ON {table|view}
  3. [WITH <dml_trigger_option>[,...n ]
  4. {FOR | AFTER | INSTEAD OF}
  5. {[INSERT][,][UPDATE][,][DELETE]}
  6. [WITH APPEND]
  7. [NOT FOR REPLICATION]
  8. AS {sql_statement [;][,...n ]| EXTERNAL NAME <method specifier [;]>}
  9. <dml_trigger_option>::=
  10.    [ENCRYPTION]
  11.    [EXECUTE AS Clause]
  12. <method specifier>::=
  13.   assembly_name.class_name.method name
复制代码
对其举行表明： 

通常需求都是很普通的，下令按照如下格式即可：
  1. CREATE TRIGGER [schema_name.]trigger_name
  2. ON table_name
  3. [AFTER/INSTEAD OF] {INSERT, UPDATE, DELETE}
  4. AS
  5. BEGIN
  6.     -- 触发器逻辑代码
  7. END
复制代码
以下是DML触发器的示例：

  1. use SJCX ;  --选用数据库
  2.   --创建测试表Employees
  3.   CREATE TABLE Employees (
  4.     ID INT PRIMARY KEY,
  5.     Name VARCHAR(50),
  6.     Salary DECIMAL(10, 2)
  7. );
  8.   
  9.   --插入数据
  10.   INSERT INTO Employees (ID, Name, Salary)
  11. VALUES
  12.     (1, 'John Doe', 5000),
  13.     (2, 'Jane Smith', 6000),
  14.     (3, 'Mike Johnson', 4500);
  15.    
  16. --创建相关表-日志表：SalaryLog
  17. CREATE TABLE SalaryLog (
  18.     EmployeeID INT,
  19.     OldSalary DECIMAL(10, 2),
  20.     NewSalary DECIMAL(10, 2),
  21.     ChangeDate DATETIME
  22. );
  24. --检查表数据
  25. select * from Employees;
  26. select * from SalaryLog;
  29. --创建触发器：
  30. CREATE TRIGGER UpdateSalaryTrigger
  31. ON Employees
  32. FOR UPDATE
  33. AS
  34. BEGIN
  35.     -- 如果薪水列被修改，则记录一条日志
  36.     IF UPDATE(Salary)
  37.     BEGIN
  38.         INSERT INTO SalaryLog (EmployeeID, OldSalary, NewSalary, ChangeDate)
  39.         SELECT d.ID, d.Salary, i.Salary, GETDATE()
  40.         FROM inserted AS i
  41.         INNER JOIN deleted AS d ON i.ID = d.ID;
  42.     END
  43. END;
  45. --触发器验证：
  46. --更新"Employees"表中员工ID为1的记录的薪水：
  47. UPDATE Employees
  48. SET Salary = 5500
  49. WHERE ID = 1;
  51. --结果查验：
  52. --使用SELECT语句来检查"SalaryLog"表中的日志记录是否生成：
  53. select * from SalaryLog;
复制代码
在触发器中，我使用了内置的UPDATE()函数来检查是否修改了"Salary"列。假如是，则将相应的旧薪水、新薪水和修改日期插入到名为"SalaryLog"的日志表中。然后我 更新"Employees"表中的1号员工的薪水，并检查"SalaryLog"表中是否出现了对应的日志记载。日志记载运行结果如下：


  1. --示例2：
  2. --创建"Customers"表
  3. CREATE TABLE dbo.Customers (
  4.     CustomerId INT PRIMARY KEY,
  5.     CustomerName VARCHAR(100),
  6.     Address VARCHAR(200)
  7. );
  10. --创建"CustomerLog"表：
  11. CREATE TABLE dbo.CustomerLog (
  12.     LogId INT IDENTITY(1,1) PRIMARY KEY,
  13.     CustomerId INT,
  14.     Action VARCHAR(10),
  15.     LogDate DATETIME
  16. );
  18. --数据检查：
  19. SELECT * FROM Customers;
  20. SELECT * FROM CustomerLog;
  24. -- 创建触发器
  25. CREATE TRIGGER dbo.LogInserts
  26. ON dbo.Customers
  27. AFTER INSERT
  28. AS
  29. BEGIN
  30.     INSERT INTO dbo.CustomerLog (CustomerId, Action, LogDate)
  31.     SELECT CustomerId, 'INSERT', GETDATE()
  32.     FROM inserted;
  33. END
  35. --触发器验证：
  36. --插入数据：
  37. INSERT INTO dbo.Customers (CustomerId, CustomerName, Address)
  38. VALUES (1, 'John Doe', '123 Main St'),
  39.        (2, 'Jane Smith', '456 Oak Ave');
  41. --结果检查：
  42. --数据检查：
  43. SELECT * FROM Customers;
  44. SELECT * FROM CustomerLog;
复制代码
该触发器表明： 

 为了测试这个触发器，我是按照以下步调举行：
 日志记载运行结果如下：


  1. --示例3：
  2. --借用上面的测试表Customers：
  4. --创建相关表DeletedCustomersLog：
  5. CREATE TABLE DeletedCustomersLog (
  6.     ID INT,
  7.     Name VARCHAR(50),
  8.     Address VARCHAR(100),
  9.     DeleteDate DATETIME
  10. );
  12. --表检查：
  13. SELECT * FROM Customers;
  14. SELECT * FROM DeletedCustomersLog;
  17. -- 创建触发器
  18. CREATE TRIGGER DeleteCustomerTrigger
  19. ON Customers
  20. AFTER DELETE
  21. AS
  22. BEGIN
  23.     -- 记录被删除的客户信息到日志表
  24.     INSERT INTO DeletedCustomersLog (ID, Name, Address, DeleteDate)
  25.     SELECT CustomerID, CustomerName, Address, GETDATE()
  26.     FROM deleted;
  27. END;
  29. --触发器验证：
  30. --尝试从"Customers"表中删除某个客户，并检查触发器是否被触发并在"DeletedCustomersLog"表中生成了相应的日志记录：
  31. DELETE FROM Customers
  32. WHERE CustomerID = 2;
  34. --结果检查：
  35. SELECT * FROM Customers;
  36. SELECT * FROM DeletedCustomersLog;
复制代码
  日志记载运行结果如下：

4.2创建DDL触发器

在SQL Server 2008中，DDL触发器（Data Definition Language triggers）是用于响应数据库的结构更改利用（如创建、修改或删除表、视图、存储过程等）的触发器。DDL触发器在实行某个DDL语句之前或之后触发，并且可以被用于验证、记载或制止这些结构更改。
DDL触发器可以根据差别的事件和条件举行触发。以下是一些DDL触发器的实行条件：

创建DDL触发器的语法格式如下：
  1. CREATE TRIGGER trigger_name
  2. ON {ALL SERVER | DATABASE}
  3. [WITH <ddl_trigger_option>[,...n ]]
  4. {FOR | AFTER}{event_type | event_group }[,...n]
  5. AS {sql_statement [;][,...n]| EXTERNAL NAME < method specifier> [;]}
  6. <ddl_trigger_option>::=
  7.   [ENCRYPTION]
  8.   [EXECUTE AS Clause]
  9. <method specifier>::=
  10.    assembly_name.class_name.method name
复制代码
对其举行表明：

以下是DDL触发器的示例：
创建一个触发器，当对库中表举行创建、修改或删除的利用时，向日志表插入记载，具体下令如下：
  1. --创建 TestTable 的测试表：
  2. -- 创建测试表
  3. CREATE TABLE TestTable (
  4.     ID INT PRIMARY KEY,
  5.     Name VARCHAR(50)
  6. );
  9. --创建LogTable 的日志表:
  10. --用于记录触发器操作
  11. -- 创建日志表
  12. CREATE TABLE LogTable (
  13.     EventDateTime DATETIME,
  14.     EventType NVARCHAR(100),
  15.     ObjectName NVARCHAR(100)
  16. );
  18. --表检查
  19. SELECT * FROM TestTable;
  20. SELECT * FROM LogTable;
  22. --创建触发器：
  23. CREATE TRIGGER MyDDLTrigger
  24. ON DATABASE
  25. FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
  26. AS
  27. BEGIN
  28.     -- 在这里编写触发器的逻辑
  30.     -- 示例：在触发器被触发时，向日志表插入一条记录
  31.     INSERT INTO dbo.LogTable (EventDateTime, EventType, ObjectName)
  32.     VALUES (
  33.             GETDATE(),
  34.             EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
  35.             EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')
  36.             )
  37. END;
  39. --备注：该触发器的示例逻辑会在任何创建、修改或删除表的操作发生时向一个名为 dbo.LogTable 的日志表插入一条记录
  41. --测试触发器
  42. --尝试执行一些包括创建、修改或删除表的操作
  43. -- 创建新表
  44. CREATE TABLE NewTable (
  45.     ID INT PRIMARY KEY,
  46.     Name VARCHAR(50)
  47. );
  49. -- 修改测试表
  50. ALTER TABLE TestTable ADD Age INT;
  52. -- 删除表
  53. DROP TABLE NewTable;
  56. --数据检查
  57. --完成这些操作后，查询日志表 LogTable 来查看触发器的执行情况：
  58. SELECT * FROM LogTable;
复制代码
通过上述触发器，当任何创建、修改或删除表的利用发生时，将会向 dbo.LogTable 日志表中插入一条记载，记载了触发事件的日期时间、事件范例和对象名称。日志记载运行结果如下： 

4.3创建登录触发器

登录触发器在遇到LOGON事件时触发。LOGON事件是在建立用户会话时引发的。触发器可以由
Transact--SQL语句直接创建，也可以由程序集方法创建，这些方法是在Microsoft.NET Framework公共语言运行时(CLR)中创建并上载到SQL Server实例的。SQL Server答应为任何特定语句创建多个触发器。
创建登录触发器的语法格式如下：
  1. CREATE TRIGGER trigger_name
  2. ON ALL SERVER
  3. [WITH <logon_trigger_option>[,..n]]
  4. {FOR | AFTER} LOGON
  5. AS {sql_statement [;][,...n ]|EXTERNAL NAME <method specifier>[;]}
  6. <logon_trigger_option>::=
  7.    [ENCRYPTION]
  8.    [EXECUTE AS Clause]
  9. <method_specifier>::=
  10.   assembly_name.class_name.method_name
复制代码
该语法是用于创建一个在全部服务器上的触发器，以下是表明：

示例1：创建一个登录触发器，该触发器拒绝TM登录名的成员登录SQL Server。具体下令如下：
  1. USE master;--将当前会话的默认数据库设置为 master
  2. GO
  3. --创建一个名为 TM 的登录账号，并设置密码为 'TMsoft'。
  4. --MUST_CHANGE 参数表示用户首次登录时必须更改密码，CHECK_EXPIRATION 参数表示密码过期策略启用
  5. CREATE LOGIN TM WITH PASSWORD ='TMsoft'MUST_CHANGE,
  6.   CHECK_EXPIRATION=ON;
  7. GO
  8. --授予登录账号 TM 对服务器状态信息 (sys.dm_exec_sessions) 的查看权限
  9. GRANT VIEW SERVER STATE TO TM;
  10. GO
  11. --创建一个名为 connection_limit_trigger 的登录触发器，并将其绑定到服务器级别的 LOGON 事件上。
  12. --触发器使用 EXECUTE AS 'TM' 语句指定以登录账号 TM 的身份执行触发器的逻辑。
  13. CREATE TRIGGER connection_limit_trigger
  14. ON ALL SERVER
  15. WITH EXECUTE AS 'TM'
  16. FOR LOGON
  17. AS
  18. BEGIN
  19. IF ORIGINAL_LOGIN()='TM'AND
  20.     (SELECT COUNT(*)FROM sys.dm_exec_sessions
  21.            WHERE is_user_process=1 AND
  22.              original_login_name='TM')>1  
  23. --判断正在登录的用户是否为 TM，然后通过查询 sys.dm_exec_sessions 视图统计当前活跃的会话数量。
  24. --如果会话数大于1，则执行回滚操作，阻止用户的登录。
  25.     ROLLBACK;
  26. END;         
复制代码
登录触发器与DML触发器、DDL触发器所存储的位置差别，其存储位置为对象资源管理器中“服
务器对象”/“触发器”。登录触发器connection_limit_trigger中的TM为登录到SQL Server中的登录名。触发器及TM所在的位置如图所示。

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

 示例2：创建登录触发器，禁止名为RestrictedUser的特定用户登录，具体下令如下：
  1. --创建登录触发器：禁止名为RestrictedUser的特定用户登录
  2. CREATE TRIGGER LoginTrigger
  3. ON ALL SERVER   -- 触发器将在服务器级别上执行，而不是特定的数据库
  4. FOR LOGON       --触发器将在用户登录到服务器时触发
  5. AS
  6. BEGIN
  7.     -- 在此处编写触发器逻辑
  9.     -- 示例：禁止特定用户登录
  10.     IF ORIGINAL_LOGIN() = 'RestrictedUser'
  11.     BEGIN
  12.         ROLLBACK;
  13.         RAISERROR ('Access Denied', 16, 1);
  14.         RETURN;
  15.     END
  16. END
复制代码
剖析逻辑：
假如触发器检测到原始登录名 (ORIGINAL_LOGIN()) 等于 'RestrictedUser'，它会实行以下利用：  

   留意：
   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触发器、登录触发器。
语法格式如下：
  1. USE YourDatabaseName;
  2. 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 视图可以大概更正确地检索与触发器相关的信息。
假如希望查询数据库中全部触发器的具体信息，建议使用以下查询：
  1. SELECT *
  2. FROM sys.triggers;
复制代码
结果如下：
这里将返回指定数据库中全部触发器的具体信息，包罗触发器名称、关联对象、事件范例等。
sys.triggers 视图：专门用于检索与触发器相关的信息。它包含数据库中全部触发器对象的元数据信息，例如触发器名称、所属表名、事件范例等。
在 SQL Server 2008 中，DDL（数据界说语言）触发器无法直接查看其界说和逻辑代码。这是因为 SQL Server 2008 不提供一种简朴的方法来检索 DDL 触发器的完整界说。然而，可以通过查询体系表来获取有关数据库中的 DDL 触发器的一些基本信息。 打开 SQL Server Management Studio (SSMS) 工具并连接到数据库实例，在 "新建查询" 窗口中，输入以下下令：
  1. USE YourDatabaseName;
  2. SELECT *
  3. FROM sys.triggers
  4. WHERE [type] = 'TR' AND parent_class_desc = 'DATABASE';
复制代码
将 YourDatabaseName 替换为要查询触发器所在的数据库名。
例如想查找我上面所创建的DDL触发器， SQL运行结果如图所示：

   留意：
  此查询将返回与数据库关联的全部 DDL 触发器的基本信息，但不会包含触发器的完整界说和逻辑代码。假如必要查看特定 DDL 触发器的具体界说和逻辑，请尝试使用其他工具、脚本或第三方插件，如 Red Gate SQL Prompt 或 ApexSQL Refactor，这些工具可以帮助我们更方便地查看和管理 DDL 触发器。 
  DDL（数据界说语言）触发器和 DML（数据利用语言）触发器是两种差别范例的触发器：

通过 SELECT * FROM sys.objects WHERE TYPE='TR' 查询，将得到包含全部范例的触发器的结果，无论是 DDL 触发器还是 DML 触发器。但是假如想进一步区分 DDL 触发器和 DML 触发器，可以参考以下查询示例：
  1. SELECT *
  2. FROM sys.triggers
  3. WHERE parent_class = 0; -- DDL 触发器
  5. SELECT *
  6. FROM sys.triggers
  7. WHERE parent_class > 0; -- DML 触发器
复制代码
运行结果如图所示：
 从结果图可观察到上述已经创建的一些触发器。发现上述查询可以根据 parent_class 列的值来区分 DDL 触发器和 DML 触发器。 
除了上述的方法查看触发器以外，也可以通过下面的一些方法举行查看：
在 SQL Server 2008 中，查看数据库中的 DML（数据利用语言）触发器：


通过上述步调，可以查看数据库中表的 DML 触发器及其相关的 T-SQL 脚本。这样就可以了解触发器的逻辑和利用，以满足我们的需求。
   留意：
  这些步调假设我们已经拥有足够的权限来查看触发器对象。假如遇到任何问题，要确保使用具有适当权限的登录账号连接到 SQL Server 实例。
  5.2修改触发器

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

修改DML触发器的语法格式如下：
  1. ALTER TRIGGER trigger_name
  2. ON table_name
  3. AFTER/INSTEAD OF {INSERT, UPDATE, DELETE}
  4. AS
  5. BEGIN
  6.     -- 触发器逻辑代码
  7. END;
复制代码
留意几点： 

例如使用ALTER TRIGGER语句修改DML触发器DeleteCustomerTrigger,当向该表中插入、修改或删除数据时给出提示信息。SQL语句如下：
  1. --修改DML触发器：DeleteCustomerTrigger
  2. ALTER TRIGGER DeleteCustomerTrigger
  3. ON Customers
  4. AFTER INSERT, UPDATE, DELETE
  5. AS
  6. RAISERROR('正在向表中插入、修改或删除数据',16,10);
复制代码
验证修改的触发器，向表中插入数据，结果如下所示：

表结果检查，如图所示：

5.2.2修改DDL触发器

修改DML触发器的语法格式如下：
  1. ALTER TRIGGER trigger_name
  2. ON DATABASE
  3. FOR {EVENT_TYPE}
  4. AS
  5. BEGIN
  6.     -- 触发器逻辑代码
  7. END;
复制代码

例如使用ALTER TRIGGER语句修改DDL触发器MyDDLTrigger,防止用户修改数据。SQL语句如下：
  1. CREATE TRIGGER MyDDLTrigger
  2. ON DATABASE
  3. FOR ALTER_TABLE
  4. AS
  5. BEGIN
  6.     RAISERROR('只有“MyDDLTrigger”触发器无效时，才可以修改表。', 16, 10);
  7.     ROLLBACK;
  8. END;
复制代码
验证已修改完成的触发器，对表举行alter table 利用，结果如图所示：

5.2.3修改登录触发器

修改登录触发器的语法格式如下：
  1. ALTER TRIGGER trigger_name
  2. ON ALL SERVER
  3. FOR LOGON
  4. AS
  5. BEGIN
  6.     -- 触发器逻辑代码
  7. END;
复制代码
留意几点：

在编写登录触发器时，通常会使用一些体系函数（如 ORIGINAL_LOGIN() 和 HOST_NAME() 等）来获取相关信息，以便举行相应的处理。
例如使用ALTER TRIGGER语句修改登录触发器connection_limit_trigger,将用户名修改为nxt,假如在此登录名下已运行3个用户会话，拒绝nxt登录到SQL Server。SQL语句如下：
  1. --修改登录触发器：
  2. ALTER TRIGGER connection_limit_trigger
  3. ON ALL SERVER
  4. WITH EXECUTE AS'NXT'
  5. FOR LOGON
  6. AS
  7. BEGIN
  8.     -- 触发器逻辑代码
  9.     IF ORIGINAL_LOGIN ()='NXT' AND
  10.     (SELECT COUNT(*) FROM SYS.dm_exec_sessions
  11.        WHERE is_user_process =1 AND
  12.          original_login_name ='NXT')>3
  13.      ROLLBACK;
  14. END;
复制代码
验证修改的登录触发器，使用用户名 "NXT" 举行登录尝试，并确保同时存在凌驾3个与该用户名相关的运动会话。
起首修改TM用户名为NXT，定名如下：
  1. --修改用户名：
  2. ALTER LOGIN TM
  3. WITH NAME = NXT;
复制代码
用户名为 "NXT" 并且与该用户名相关的运动会话数凌驾3个，则触发器将触发 ROLLBACK 语句，取消登录尝试，并提供一个错误消息。如图所示：

5.3重定名触发器

重定名触发器可以使用sp_rename体系存储过程实现。使用sp_rename体系存储过程重定名触发器
与重定名存储过程雷同。但是使用该体系存储过程重定名触发器，不会更改sys.sql modules类别视图的definition(用于界说此模块的SQL文本)列中相应对象名的名称，所以建议用户不要使用该体系存储过程重定名触发器，而是删除该触发器，然后使用新名称重新创建该触发器。
可以使用以下语法格式来重定名触发器：
  1. sp_rename 'old_trigger_name', 'new_trigger_name', 'OBJECT';
复制代码
old_trigger_name 是要重定名的现有触发器的名称，new_trigger_name 是要修改为的新触发器名称。
留意几点：

 例如：使用sp_rename将触发器DeleteCustomerTrigger重定名为DeleteCusTrigger。SQL语句如下：
  1. --重命名触发器：
  2. sp_rename 'DeleteCustomerTrigger','DeleteCusTrigger'
复制代码
检查修改后的触发器，如图所示：

5.4禁用与启用触发器

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

使用以下语法格式来禁用触发器：
  1. DISABLE TRIGGER {trigger_name | ALL}
  2. ON {table_name | view_name}
复制代码

 留意几点：

例1：使用DISABLE TRIGGER语句禁用DML触发器DeleteCusTrigger，SQL语句如下：
  1. --禁用DML触发器：
  2. DISABLE TRIGGER DeleteCusTrigger
  3. ON CUSTOMERS;
复制代码
例2：使用DISABLE TRIGGER语句禁用DDL触发器MyDDLTrigger，SQL语句如下：
  1. --禁用DDL触发器：
  2. DISABLE TRIGGER MyDDLTrigger
  3. ON DATABASE;
复制代码
例3：使用DISABLE TRIGGER语句禁用登录触发器connection_limit_trigger，SQL语句如下：
  1. --禁用登录触发器：
  2. DISABLE TRIGGER connection_limit_trigger
  3. ON ALL SERVER;
复制代码
检查已经禁用的各个触发器的状态，SQL语句如下：
  1. --查看触发器状态：0-启动；1-禁用
  2. SELECT
  3.     name AS trigger_name,
  4.     is_disabled
  5. FROM
  6.     sys.triggers
  7. WHERE
  8.     is_disabled = 1;
  10. --查看登录触发器的状态
  11. SELECT
  12.     name AS trigger_name,
  13.     is_disabled
  14. FROM
  15.     sys.server_triggers
  16. WHERE
  17.     is_disabled = 1;
复制代码
 实行结果如图所示：

5.4.2启用触发器

启用触发器并不是重新创建它。已禁用的DDL、DML或登录触发器可以通过实行ENABLE TRIGGER语句重新启用。语法格式如下： 
  1. ENABLE TRIGGER {trigger_name | ALL}
  2. ON {table_name | view_name}
复制代码

留意几点：

例1：使用ENABLE TRIGGER语句启用DML触发器DeleteCusTrigger，SQL语句如下：
  1. --启用DML触发器：
  2. ENABLE TRIGGER DeleteCusTrigger
  3. ON CUSTOMERS;
复制代码
例2：使用ENABLE TRIGGER语句启用DDL触发器MyDDLTrigger，SQL语句如下：
  1. --启用DDL触发器：
  2. ENABLE TRIGGER MyDDLTrigger
  3. ON DATABASE;
复制代码
例3：使用ENABLE TRIGGER语句启用登录触发器connection_limit_trigger，SQL语句如下：
  1. --启用登录触发器：
  2. ENABLE TRIGGER connection_limit_trigger
  3. 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触发器的语法格式如下：
  1. DROP TRIGGER [IF EXISTS] {trigger_name | table_name.trigger_name}
复制代码

留意几点：

例如使用DROP TRIGGER语句禁用DML触发器DeleteCusTrigger，SQL语句如下：
  1. --删除DML触发器
  2. DROP TRIGGER DeleteCusTrigger
复制代码
(2)删除DDL触发器
删除DDL触发器的语法格式如下：
  1. DROP TRIGGER [IF EXISTS] {trigger_name | ddl_trigger_name}
  2. ON {database_name | ALL SERVER}
复制代码

留意以下几点：

例如使用DROP TRIGGER语句禁用DML触发器MyDDLTrigger，SQL语句如下：
  1. ----删除DDL触发器
  2. DROP TRIGGER MyDDLTrigger
  3. ON DATABASE;
复制代码
(3)删除登录触发器
删除登录触发器的语法格式如下： 
  1. DROP TRIGGER [IF EXISTS] {trigger_name | logon_trigger_name}
  2. ON ALL SERVER
复制代码

留意几点：

例如使用DROP TRIGGER语句禁用DML触发器connection_limit_trigger，SQL语句如下：
  1. --删除登录触发器
  2. DROP TRIGGER connection_limit_trigger
  3. ON ALL SERVER;
复制代码
  提示：
  一旦触发器被删除，相关的元数据信息将不再存在于体系视图中。因此，无法通过查询体系视图来确定触发器是否已经被删除。假如必要确认触发器是否已被删除，可以尝试实行与该触发器相关的利用（如插入、更新或删除数据），并观察是否会产生预期的触发器行为。假如触发器不存在，相关的触发器逻辑将不会被触发。
  一定要留意，在删除触发器之前，最好备份数据库，以便在必要时举行恢复。
  5.5.2.SQL Server Management Studio手动删除触发器

手动删除触发器的步调如下：
(1)打开SQL Server Management Studio,并连接到SQL Server2008中的数据库。
(2)睁开“对象资源管理器”中触发器所在位置。如要删除创建在db2008数据库的触发器，则睁开如图所示的树形结构。

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

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

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

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4