SQL SERVER触发器详解
作者:.NET开发菜鸟 发布时间:2024-01-22 01:50:00
触发器是一种特殊的存储过程,触发器主要是通过事件进行触发而被自动调用执行,而存储过程必须通过存储过程的名称被调用。
一、触发器的定义
触发器是在对表进行插入、更新或删除操作时自动执行的特殊存储过程。触发器通常用于强制业务规则,触发器是一种高级约束,可以定义比CHECK约束更为复杂的约束:可以执行复杂的SQL语句(if/while/case),可以引用其他表中的列。触发器定义在特定的表上,与表相关,自动触发执行,不能直接调用,是一个事务(可回滚)。
二、触发器分类
SQL SERVER中触发器可以分为两类:DML触发器和DDL触发器,DML触发器针对表,DDL触发器会影响多种数据定义语言语句而触发,这些语句有create、alter、drop语句。
DML触发器分为:
1、after触发器(之后触发)
a、insert触发器
b、update触发器
c、delete触发器
2、instead of触发器(之前触发)
after触发器要求只有执行某一操作(insert、update、delete)之后触发器才能被触发,且只能定义在表上。而instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身,其优先级高于触发语句的执行。
触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)
这两张表是逻辑表也是虚表,触发器触发时系统自动在内存中创建这两张表,不会存储在数据库中。这两张表都是只读的,不允许修改。这两张表的结果总是与被触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。inserted表临时保存了插入或更新后的记录行,可以从inserted表中检查插入的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚插入操作。deleted表临时保存了删除或更新前的记录行,可以从deleted表中检查被删除的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚删除操作。update数据的时候是先删除表记录,然后插入一条记录,在inserted和deleted表就都有update后的数据记录了。
inserted表和deleted表存放的信息:
修改操作 | inserted表 | deleted表 |
增加(INSERT)记录 | 存放新增的记录 | 无 |
删除(DELETE)记录 | 无 | 存放被删除的记录 |
修改(UPDATE)记录 | 存放更新后的记录 | 存放更新前的记录 |
三、创建触发器
语法:
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL语句
GO
WITH ENCRYPTION
表示加密触发器定义的SQL文本
DELETE, INSERT, UPDATE
指定触发器的类型
1、创建insert类型的触发器
插入触发器
--GradeInfo表中插入一条数据,MyStudentInfo表中插入一条记录
IF (object_id('tr_insert','tr') is not null)
drop trigger tr_insert
GO
CREATE trigger tr_insert
on GradeInfo
after insert --插入触发
as
begin
--定义变量
declare @GradeId int
--在inserted表中查询已经插入记录信息
select @GradeId=id from INSERTED
--MyStudentInfo表中插入数据
insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)
print '插入成功!'
end
插入数据
insert INTO GradeInfo VALUES (11,'C++')
查询数据
select * from MyStudentInfo where GradeId=11
2、delete触发器
删除MyStudentInfo表中的数据,插入备份表
--删除MyStudentInfo表中的数据,插入备份表
IF (object_id('tr_Delete','tr') is not null)
drop TRIGGER tr_Delete
GO
CREATE trigger tr_Delete
on MyStudentInfo
for delete
as
begin
print '正在备份数据......'
IF (object_id('MyStudentInfo_Back','U') is not null)
--存在表,直接插入数据
insert INTO MyStudentInfo_Back SELECT * from DELETED
else
select * into MyStudentInfo_Back from DELETED
PRINT '备份完成'
end
删除前查询MyStudentInfo表数据
select * from MyStudentInfo
删除id=9的数据
delete FROM MyStudentInfo where Id=9
查询备份表数据
select * from MyStudentInfo_Back
3、update触发器
IF (object_id('tr_Update','tr') is not null)
drop TRIGGER tr_Update
GO
CREATE trigger tr_Update
on MyStudentInfo
for update
as
begin
--声明变量,存储更新前和更新后的姓名
declare @OldName varchar(16),@NewName varchar(16)
select @OldName=name from DELETED
print '更新前姓名:'+@OldName
select @NewName=name from INSERTED
print '更新后姓名:'+@NewName
end
把张三更新为"张三测试"
update MyStudentInfo SET Name='张三测试' where Id=1
update更新列级触发器
--update更新列级触发器
IF (object_id('tr_update_column','tr') is not null)
drop TRIGGER tr_update_column
GO
CREATE trigger tr_update_column
on GradeInfo
for update
as
begin
IF(update(id))
begin
print '系统提示:主键ID不能更新'
rollback
end
end
更新id列
update GradeInfo SET Id=15 where Id=4
4、instead of触发器
instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身的内容,其优先级高于定义的SQL语句的执行
语法:
create trigger tgr_name
on table_name
with encryption
instead of update...
as
begin
T-SQL
end
创建instead of触发器
--创建instead of触发器
/*MyStudentInfo表里面插入数据之前,先判断GradeInfo表中是否有对应的班级ID,如果没有,不允许插入,如果存在,则插入 */
IF (object_id('tr_insteadOf','tr') is not null)
drop TRIGGER tr_insteadOf
GO
CREATE trigger tr_insteadOf
on MyStudentInfo
instead of insert
as
begin
IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED))
print '该班级存在,可以插入'
else
begin
print '该班级不存在,不可以插入'
rollback
end
end
测试1,插入不存在的班级id
insert INTO MyStudentInfo (GradeId) VALUES (15)
测试2,插入存在的班级id
insert INTO MyStudentInfo (GradeId) VALUES (5)
DDL触发器
create trigger tr_DDL on database
for DROP_TABLE,ALTER_TABLE
as
begin
print '别想着删库!好好打你的代码'
rollback --回滚
end
测试删除表
drop TABLE MyStudentInfo
测试修改表结构
alter table MyStudentInfo
alter column Name varchar(32)
禁用DML触发器
disable trigger tr_insteadOf on MyStudentInfo
启用DML触发器
enable trigger tr_insteadOf on MyStudentInfo
禁用DDL触发器
disable trigger tr_DDL on database
启用DDL触发器
enable trigger tr_DDL on database
来源:https://www.cnblogs.com/dotnet261010/p/5924238.html
猜你喜欢
- 一、下载SQL Server 2008 R2安装文件cn_sql_server_2008_r2_enterprise_x86_x64_ia6
- Mysql参数优化对于新手来讲,是比较难懂的东西,其实这个参数优化,是个很复杂的东西,对于不同的网站,及其在线量,访问量,帖子数量,网络情况
- 1.文本string:通用字符串操作re:正则表达式操作difflib:差异计算工具textwrap:文本填充unicodedata:Uni
- 索引是以表列为基础的数据库对象。索引中保存着表中排序的索引列,并且纪录了索引列在数据库表中的物理存储位置,实现了表中数据的逻辑排序。通过索引
- 前沿对于iOS开发不要随便拆卸系统自带的Python,因为有很多 library 还是使用 Python2.7。1 安装Xcode1.1 A
- 前言docopt 是一个开源的库,代码地址:https://github.com/docopt/docopt。它在 README 中就已经做
- 我想大多写web的朋友应该和我一样,正则是不可少的,可是每次到用时去百度一下,也麻烦,存在电脑里也得找半天~换了电脑还是得靠google了~
- 基于smtplib包制作而成,但在实践中发现一个不知道算不算是smtplib留的一个坑,在网络断开的情况下发送邮件时会抛出一个socket.
- 本文实例讲述了Python基于Pymssql模块实现连接SQL Server数据库的方法。分享给大家供大家参考,具体如下:数据库版本:SQL
- 本文实例讲述了PHP共享内存使用与信号控制。分享给大家供大家参考,具体如下:共享内存共享内存的使用主要是为了能够在同一台机器不同的进程中共享
- 1.2 本篇文章内容概要1.3 本篇文章内容概括在SQL语句中,关于表连接,若按照表的数量来划分,可以划分为单表连接、两表连接和两表以上连接
- 目标:目标文件为一个float32型存储的二进制文件,按列优先方式存储。本文使用Python读取该二进制文件并使用matplotlib.py
- python读写excel文件有很多种方法:用xlrd和xlwt进行excel读写用openpyxl进行excel读写用pandas进行ex
- asp如何实现当前月份距离以前某个时间的月份数 如今天是2011年1月份,我想知道离2010年3月,计算这中间一共是几个月 最佳答案 <
- 这段代码的效果具体是输入标题和内容,点击发布把消息发布出去,并使最新的消息始终在内容的最上面,代码为:<!DOCTYPE html&g
- 效果知识点:css3画气球, 自定义属性运用,随机阵列, DOM元素操作,高级回调函数与参数复传,动态布局,鼠标事件,定时器运用,CSS3新
- 运行效果:完整源码:##import libraryfrom tkinter import *import timefrom playsou
- 先看看关于call()的官方解释,“调用一个对象的一个方法,以另一个对象替换当前对象。”,看了这样的解释,或许让你更摸不着头脑了。看例子:v
- 突然发现, pycharm 2020.2都出来了哈, 现在jetbrain团队对中文用户也比较友好, 比以前更加适合小白了再就是很多类似的教
- 本文实例为大家分享了pygame贪吃蛇游戏的具体代码,供大家参考,具体内容如下1.准备工作我们已经初始化了一个400*400的界面,为方便看