SQL SERVER触发器

本文深入解析SQL Server触发器,涵盖触发器的概念、分类、工作原理及语法结构,详细介绍了DML、DDL、CLR及登录触发器的特性与应用场景。

sqlserver触发器详解

触发器是一系列的T-SQL编写完成,是一种特殊的存储过程,
–触发器只能由数据库的特定事件来触发,所谓的的特定事件主要包括以下几种类型
(1)用户在指定的表或视图中进行DML操作
INSERT 操作,在特定的表或视图中添加数据
UPDATE 操作,对特定的表或视图修改数据
DELETE 操作,删除特定的表或视图数据
(2)用户进行DDL操作
CREATE 创建对象
ALTER 修改对象
DROP 删除对象
(3)用户进行LOGON(登录)操作
与SQL SERVER 实例建立连接
触发器分类
DML触发器:又被称为数据操作语言触发器作用在表或视图上,在对表或视图操作时会激发该类型触发器
该类型触发器包括
AFTER触发器:在 INSERT,UPDATE,DELETE,执行操作后触发,
INSTEAD OF 触发器:此类型触发器会用触发器本身操作替换原来的触发器(INSERT…)通常作用在视图上,利用该触发器视图可以变成可更新视图。
DDL触发器:被称为定义语言触发器,当 CREATE,ALTER,DROP 队象进行其他DDL操作时会激发该触发器,利用它可以影响数据库业务规则。
CLR触发器:在 .NET FRAMEWORK中创建,不像其他触发器需要执行T-SQL过程
登录触发器:当于SQL SERVER实例建立连接时触发
触发器工作原理
触发器的原理涉及到两张虚拟表,分别是INSERTED表和DELETED表
INSERT 操作触发器当操作数据时,会在数据表和 INSERTED 表同时放入数据,利用INSERTED表可以得到已经插入的数据
DELETE 操作触发器。当从数据库删除数据时。数据首先被放入DELETED表中,该表是一张存放了已经删除的数据虚拟表。在触发器中可以调用该表的数据。
UPDATE 操作触发器。和其他两种不同,当对触发器所在的表执行UPDATE语句时,元数据会被转移到DELETED表中,而修改后的数据则被插入到INSERTED表中最后
触发这两个表的数据,进行更新。

(1)数据操纵语言DML触发器的语法结构

  CREATE TRIGGER [dbo.]triggeres
  on{table|view}
  {WITH<trigger_option>[,...n]}
  {FOR|AFTER|INSERT OF}
  {[INSERT][,][UPDATE][,][DELETE]}
  [WITH APPEND]
  [NOT FOR REPLICATION]
  AS {SQL_STATEMENT[;][....n]|EXTERNAL NAME assembly_name.class_name.method_name[;]}
  <trigger_option> ::=
  [ENCRYPTION]
  [EXECUTE AS Clause]

–语法说明
第1行:CREATE TRIGGER表示创建触发器的关键字 dbo项表示触发器所属的架构名称 triggeres表示触发器名称
第2行 on{table|view},触发器所作用的表或视图,DML类型触发器不能作用在局部或全局临时表上
第3行 FOR|AFTER 项:AFTER 表示触发器被激发的时机。它在SQL所有的操作中完成,并且约束检查完成后被激发,默认是 AFTER
INSERT OF项:表示替代类型触发器对每个 INSERT, UPDATE 或 DELETE 语句只能定义一个 insert of 触发器
第4行 [INSERT][,][UPDATE][,][DELETE]项:激发触发器的操作,这里可以选取任意组合。
第5行 WITH APPEND 项:指定添加一个已有类型的触发器,但如果显示声明了 AFTER 触发器,或触发器类型是 insert of 时则不能使用该项
第6行 NOT FOR REPLICATION 项:当复制代码修改涉及触发器表时,不执行触发器。
第7行 SQL_STATEMENT 项:可以确定触发器的具体操作的判断条件和操作 EXTERNAL NAME assembly_name.class_name.method_name[;]项:针对CLR触发器,指定
程序集与触发器绑定方法。
[ENCRYPTION] 项: 表示对创建触发器的语句进行模糊处理。
EXECUTE AS 项:指定用于执行该触发器的安全上下文。
–数据库定义语言DDL触发器语法结构

CREATE TRIGGER TRIGGER
  ON {ALL SERVER|DATABASE }
  {WITH<DDL_trigger_option>[,...n]}
   {FOR|AFTER}{event_TYPE|event_group}[,...n]
as {SQL_STATEMENT[;][....n]|EXTERNAL NAME assembly_name.class_name.method_name[;]}
  <trigger_option> ::=
  [ENCRYPTION]
  [EXECUTE AS Clause]

第1行:CREATE TRIGGER:表示创建触发器的关键字 TRIGGER 表示触发器名称
第2行 ON {ALL SERVER| },表示触发器的范围是整个服务器 ON {ALL DATABASE| }表示触发器的范围是当前数据库
第3行 FOR|AFTER 项:AFTER 表示触发器被激发的时机。默认是 AFTER
第5行 event_TYPE项:激发触发器DDL事件名称 event_group:预定义T-SQL语言事件分组名称
第6行 SQL_STATEMENT项:可以确定触发器具体操作的判断条件和操作,EXTERNAL NAME assembly_name.class_name.method_name[;]项:针对CLR触发器,指定
程序集与触发器绑定方法。
第7,8,9行
[ENCRYPTION] 项: 表示对创建触发器的语句进行模糊处理。
–登录触发器语法结构

 CREATE TRIGGER trigger
  on ALL SERVER
  {WITH<LOGON_trigger_option>[,...n]}
  {FOR|AFTER}LOGON
  as {SQL_STATEMENT[;][....n]|EXTERNAL NAME assembly_name.class_name.method_name[;]}
  <trigger_option> ::=
  [ENCRYPTION]
  [EXECUTE AS Clause]

FOR|AFTER 项 :默认是after这里表示登录后被触发

### SQL Server 触发器概述 SQL Server中的触发器是一种特殊类型的存储过程,在特定的数据操作事件(如`INSERT`、`UPDATE`或`DELETE`)发生时会自动执行[^3]。触发器的主要用途是维护数据库的一致性和完整性。 以下是关于创建和使用SQL Server触发器的一些基本概念和示例: --- ### 创建Insert类型触发器的语法 创建一个简单的`INSERT`类型触发器,可以在指定表上定义逻辑以响应新记录的插入行为。其基础语法规则如下所示[^1]: ```sql CREATE TRIGGER trg_InsertExample ON target_table AFTER INSERT -- 可替换为FOR UPDATE, FOR DELETE等 AS BEGIN -- 在此编写Transact-SQL代码处理业务需求 END; ``` 上述脚本中指定了触发器名称(`trg_InsertExample`)及其作用的目标表(`target_table`)。通过设置`AFTER INSERT`关键字表明该触发器将在每次向目标表成功插入一条或多条记录之后运行。 --- ### 利用Inserted与Deleted临时表实现复杂逻辑 为了增强触发器的功能并支持更复杂的场景分析,SQL提供了两个内置的虚拟表——`inserted`和`deleted`。这些表格分别保存了当前事务期间涉及变动前后的原始数据副本[^2]。 #### 示例:防止非法价格更新 假设有一个产品库存管理系统的订单明细表(OrderDetails),其中包含字段OrderID、ProductID及UnitPrice。现在希望构建这样一个规则:如果有人试图降低任何已售商品的价格,则阻止这种更改的发生。 下面展示了一个满足前述条件的设计方案: ```sql -- 创建名为Prevent_Price_Decrease 的Update Trigger CREATE TRIGGER Prevent_Price_Decrease ON OrderDetails INSTEAD OF UPDATE AS BEGIN IF EXISTS ( SELECT * FROM inserted i JOIN deleted d ON i.OrderDetailID=d.OrderDetailID AND i.UnitPrice<d.UnitPrice ) RAISERROR('不允许减少单价', 16 ,1); ELSE IF NOT EXISTS ( SELECT * FROM inserted ) RETURN ; ELSE UPDATE od SET UnitPrice=i.UnitPrice FROM OrderDetails od INNER JOIN inserted i ON od.OrderDetailID =i.OrderDetailID WHERE i.UnitPrice >=d.UnitPrice END; GO ``` 在此例子中运用到了`EXISTS()`函数检测是否存在违反规定的情况;同时利用`JOIN`连接原状态(deleted)同最新提交(inserted)对比差异之处。最后采用`INSTEAD OF`代替默认动作完成定制化控制流程. --- ### 加密触发器提升安全性 有时出于安全考虑可能需要隐藏某些敏感商业逻辑或者保护知识产权不受侵犯。这时可以通过添加选项`WITH ENCRYPTION`来编译加密版本的触发程序体内容: ```sql CREATE TRIGGER SecureTriggerDemo WITH ENCRYPTION ... ``` 这样即使拥有管理员权限也无法轻易查看到实际源码细节除非掌握解密方法论。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值