SQL SERVER 学习笔记（触发器）

最新推荐文章于 2023-06-08 22:10:12 发布
最新推荐文章于 2023-06-08 22:10:12 发布
阅读量1.8k 收藏 3
点赞数 1
分类专栏： SQL Server基础知识 文章标签： server sql delete 存储 数据库 insert
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chen_linbo/article/details/5664787
版权

什么是触发器

触发器是一种由事件驱动的特殊存储过程，是独立存储在数据库里的对象，就像数据表一样。与存储过程的区别是：存储过程是通过其他程序来调用执行的；而触发器是通过事件来触发的，当某个事件发生是，触发器就自动的被触发运行。

一．触发器的种类

    SQL SERVER 2005版本支持4种触发器：INSERT、DELETE、UPDATE和INSTEAD OF触发器。

二．触发器执行的环境

触发器执行的环境既是执行触发器所需要的条件。有三个条件：触发器本身的定义、触发器所定义的目标表和两个测试表（Insert表和Delete表），也称临时表。

Deleted表用来存储执行DELETE和UPDATE语句时提取出来的数据，当执行此两语句时，相关的行数据从Trigger表中移动到Delete表中。

Insertd表用来存放执行INSERT和UPDATE语句时提取出来的数据。

假设有个SCHOOL数据库中student表，PRIMARY  KRY为Sno如下：

USE  SCHOOL

GO

CREATE  TABLE  student

(

Sno   char(8)  PRIMARY  KEY,  

Sname   char(5),

Cname   char(10),

Grade   int

)

GO

--插入数据

INSERT  INTO  student  VALUES('1001','李浩','英语',89)

INSERT  INTO  student  VALUES('1001 ','李浩 ','数字电子 ',90 )

INSERT  INTO  student  VALUES('1002 ','汪林 ','计算机导论 ',85 )

INSERT  INTO  student  VALUES('1009 ','郝梦 ','大学物理 ',80 )

INSERT  INTO  student  VALUES('1021 ','郭晓 ','C++程序设计 ',81 )

INSERT  INTO  student  VALUES('1013 ','黄玲 ','计C#程序设计 ',93 )

INSERT  INTO  student  VALUES('1013 ','黄玲 ',计算机网络 ',79)

GO

三．触发器的创建以及使用

1.INSERT触发器

此触发器是针对在进行插入数据后的一些操作，包括添加、删除、更新、查询。

 

添加

CREATE TRIGGER  insert_stu

ON  student

AFTER  INSERT

AS

INSERT  INTO  student  VALUES('1015','李晓莉','邓小平理论','85')

PRINT  ' 数据插入成功 '

 

删除

CREATE  TRIGGER delete_stu

ON  student

AFTER  INSERT

AS

DELETE student

FROM student

WHERE  Sno='1002'

PRINT  ' 数据删除成功 '

 

更新

CREATE  TRIGGER  update_stu

ON  student

AFTER  INSERT

AS

UPDATE  student

SET  Grade=Grade+10

WHERE  Sno='1009'

 

查询

CREATE  TRIGGER select_stu

ON  student

AFTER  INSERT

AS

SELECT * FROM  student

上面这四个触发器语句，在执行一个插入语句后会自动触发，即自动执行，他们都有一个共同的特点：没限制条件。

下面来创建一个带限制条件的触发器语句，当想学生信息表中插入数据时，要求插入学生必须是院系好为481208，否则插入不成功：

CREATE  TRIGGER  insert_Return

ON  studentINFO

FOR  INSERT

AS

DECLARE  @stp_no char(8)

SELECT  @sto_no=stoNo

FROM   studentINFO

IF(@stp_no!='481208')

BEGIN

ROLLBACK TRANSACTION

RAISEERROR('插入学生的院系编号必须是'481208'，请输入'，16，1，@stp_no)

END

当插入学生的院系编号不是481208时插入不成功。插入不符合条件，触发器就会执行ROLLBACK TRANSACTION语句，取消所有操作。

 

2.UPDATE 触发器

使用此触发器，可以更新数据库里的数据。UPDATE 触发器首先将原始数据行移动到逻辑Deleted表中，然后把一新行插入到Inserted表中，最后再将这行查到触发器中。也可以对数据库表进行增、删、改、查（又名珍、珠、奶、茶）。

 

插入

CREATE  TRIGGER  insert_stu

ON  student

AS

INSERT  INTO  student  VALUES('1007','王平','毛泽东概论','88')

PRINT  ' 数据插入成功 '

 

删除

CREATE  TRIGGER delete_stu

ON  student

AS

DELETE  student

FROM  student

WHERE  Sno='1009'

 

修改

CREATE  TRIGGER update_stu

ON  student

AS

UPDATE   student

SET   Grade=Grade+2

WHERE  Sname='汪林'

 

查询

    CREATE  TRIGGER select_stu

ON  student

AS

SELECT  TOP(5)  FROM   student

这些触发器执行成功后就会自动存储，要使触发器发生作用，只有在创建它之后就执行UPDATE语句。不能存储相同名字的触发器，系统会报错，你的操作将不能成功。

下面我们来创建一个复杂的UPDATE触发器，其实是运用了一些T-SQL知识。新创建一个触发器Copy_UpdateInfo，当你更新sno时，触发器提示你“信息不能更新”，并回滚事务；如果更新Cname或者Grade时，触发器会将被更新的学生信息写入到一个新表里，首先得创建这个表：

CREATE  TABLE  Temp_studentInfo

(

sno  CHAR(8),

sname  CHAR(5),

cname   CHAR(8),

grade  int

)

 

下面创建触发器Copy_UpdateInfo：

CREATE  TRIGGER  Copy_UpdateInfo

ON  student

FOR  UPDATE

AS

IF(COLUMNS_UPDATE() &1) > 0

BEGIN

    ROLLBACK  TRANSANCTION

    RAISERROR( ' 学生学号不能更新! ' , 16 , 1)

END

IF(COLUMNS_UPDATE()  &14) > 0

BEGIN

    INSERT  INTO  Temp_studentInfo

                          (sno,

                           sname,

                           cname,

                           grade  )

                           SELECT stu.Sno , 

                                   stu.Sname,

                                   stu.Cname,

                                   stu.Grade

                           FROM  student  AS  stu

END

当我们试着更新学号时，DBMS显示“学生学号不能更新”的提示，即更新失败，触发器执行ROLLBACK  TRANSANCTION回滚操作。当更新课程名或者成绩时，如下：

UPDATE  student

SET  Cname='汇编语言'

WHERE  Sno='1009'

GO

执行成功后，再执行T-SQL代码：SELECT  *  FROM  Temp_studentInfo  ，我们将会得到更新过的学生信息。

 

3.DELETE触发器 

DELETE触发器将删除的行数据插入到Delete表中，并决定Delete表中的行数据是否要执行触发操作。也可用此触发器对数据库表进行增、删、改、查（又名珍、珠、奶、茶）。

 

插入

CREATE  TRIGGER  deleteInsert_stu

ON  student

AFTER  DELETE

AS

INSERT  INTO  student

VALUES( ' 10010 ' , ' 李明 ' , ' 网络工程 ' , 88 )

 

删除

CREATE  TRIGGER  delete_stu

ON  student

FOR  DELETE

AS

DECLARE  @rowcount  int

SELECT  @rowcount =@@ROWCOUNT

IF  @rowcount > 1

   BEGIN

      ROLLBACK  TRANSANCTION

      RAISERROR( ' 你要删除的数据记录为%d条，一次只能删除一行数据，删除失败！' , 16 , 1 , @rowcount )

END

 

修改

CREATE  TRIGGER  deleteUpdate_stu

ON  student

AFTER  DELETE

AS

UPDATE  student

SET  Grade=Grade+10

WHERER  Grade <  60

 

查询

USE  SCHOOL

GO

DECLARE  @p  AS  int

SET  @p=10

SELECT  TOP ( @p ) *

FROM  student

GO

针对上面四个触发器，每执行一个UPDATE语句都会触发其中之一，每个触发器要在建立在执行语句之前创建，语句执行成功后立即将此触发器删除。

 

4.INSTEAD  OF 触发器

INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。例如，通常不能在一个基于连接多表的视图上进行DELETE操作。然而，可以编写一个INSTEAD OF DELETE触发器来实现删除，这样就可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中。相似地，在INSTEAD OFUPDATE触发器或者INSTEAD OF INSERT触发器中，你可以访问inserted表中的新行。

下面通过例子来说明INSTEAD OF触发器的用法：

--创建一个学生信息表

CREATE  TABLE  TempStudents

(

Sno  char(5),

Sname  char(10),

Sex  char(2),

Sclass  char(20)

)

--创建一个学生成绩表

CREATE  TABLE  TempAchies

(

Sno  char(5),

Suname  char(10),

Suachieve  float,

Smark  char(50)

)

--创建一个视图

CREATE  VIEW  TempStu_View

AS

SELECT  TempStudents.Sno , TempStudents.Sname , TempAchies.Suname , TempAchies.Suachieve

FROM  TempAchies  INNER  JOIN  TempStudents

ON  TempAchies.Sno = TempStudents.Sno

基于多连接的视图不允许向其插入数据，当我们输入下面的语句：

INSERT  INTO  TempStu_View

VALUES(' 95010 ' , ' 刘栋 ' , ' 计算机导论 ' , ' 91 ' )

执行成功后，DBMS显示“视图TempStu_View不可更新，因为修改会影响多个表”的提示。

--创建INSTEAD OF触发器，向视图中出入数据

CREATE  TRIGGER  View_Insert

ON  TempStu_View

INSTEAD  OF   INSERT

AS

BEGIN

   BEGIN

       INSERT  INTO  TempStudents(Sno , Sname)

       SELECT  Sno , Sname

       FROM  INSERTED

   END

   BEGIN

       INSERT  INTO  TempAchies (Sno , Suname , Suachieve)

       SELECT  Sno , Suname , Suachieve

       FROM  INSERTED

    END

END

 

执行成功既触发器创建成功，现在我们可以向视图TempStu_View里面插入数据了，执行下面的代码：

INSERT  INTO  TempStu_View

VALUES(' 95010 ' , ' 刘栋 ' , ' 计算机导论 ' , ' 91 ' )

插入成功后，再查看视图中的信息：

SELECT  *  FROM  TempStu_View

你将会看到你插入的数据。

 

5.触发器的修改与删除

修改

我们将修改上面创建的delete_stu触发器

ALTER  TRIGGER  delete_stu

ON  student

AFTER  INSERT

AS

DELETE  student

FROM  student

WHERE  Cname= '  黄玲 '

PRINT  ' 数据删除成功 '

删除

语法格式为 ：DROP  TRIGGER  trigger_name

下面我们删除delete_stu触发器

DROP  TRIGGER  delete_stu

代码执行后，触发器delete_stu被删除成功

304的的哥
关注
专栏目录
Sql Server 触发器笔记
TuTuTu_Tu的博客
05-19 1897
目录1.定义2.作用3.After触发器3.1insert触发器3.2update触发器3.3delete触发器4.Instead of触发器 1.定义 触发器是一种专用类型的存储过程，它被捆绑到SQL Server 的表格或者视图上。通过事件进行触发被自动调用执行。 触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的
数据库笔记（SQL之触发器）
zsk843的博客
04-20 527
触发器相关笔记
SQL触发器课堂笔记
csdcainiao的博客
06-11 297
--创建触发器 --触发器 保证数据完整性，与表事件相关的特殊的存储过程，由事件触发 --比如当对一个表进行操作时（alter，delete，update） DELETE Student WHERE Sno='1201011102' SELECT * FROM Student WHERE Sno='1201011102' --插入一条新记录 INSERT INTO Student (Sno,Sname,Ssex,EntranceTime,Classno) VALUES ('11099','字符串',
数据库实验之触发器
某人辛木的博客
06-03 1268
关于触发器的一些操作练习。
在stusys 数据库的student表创建一个触发器T_deleteStudentScore，当删除表student中某个学生的记录时，同时将score表中与该学生有关的数据全部删除。
weixin_63502892的博客
06-03 1175
sqlserver2005学习笔记之触发器简介.pdf
10-04
SQL Server 2005中的触发器是一种特殊类型的存储过程，它会在执行特定的T-SQL语句（如INSERT、UPDATE、DELETE）时自动触发执行。触发器的主要作用在于增强数据的完整性和有效性，它们能完成比约束更复杂的数据验证，...
SQL Server学习笔记-应用举例
10-19
本笔记主要围绕SQL Server中的T-SQL（Transact-SQL）进行学习，它是一种用于操作SQL Server数据库的编程语言。T-SQL不仅包括了SQL的标准语法，还扩展了许多特有的功能，使得数据库的管理和开发更加便捷。以下是根据...
SQL Server精华 (CHM)_sqlserver_SQLServer笔记_
09-29
《SQL Server精华 (CHM)_sqlserver_SQLServer笔记_》是一部综合性的SQL Server学习资源，旨在帮助用户深入理解和掌握Microsoft SQL Server数据库管理系统的核心概念、功能和最佳实践。这部笔记涵盖了一系列关键知识...
SQL server-2017-学习笔记.docx
09-08
在SQL Server 2017的学习中，我们首先需要了解SQL Server的基础操作，这包括了基本的增删改查操作，比如INSERT、DELETE、UPDATE和SELECT。这些是数据库管理中最常用的操作，用于处理数据的存取和修改。 接着，我们...
sql server 2005学习笔记之触发器
02-28
SQL Server 2005中的触发器是一种特殊类型的存储过程，它们在特定的T-SQL语句执行时自动触发，增强了数据库的逻辑控制能力。触发器主要分为两类：DML触发器和DDL触发器。 DML触发器是针对数据操纵语言（Data ...
数据库触发器的用法
《春馨梦》的专栏
05-13 2338
CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ]{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATIO
MySQL学习笔记
m0_70415287的博客
07-15 587
触发器的创建以及应用例题
【无标题】
最新发布
zhou202109525的博客
06-08 328
【例11.5】 在stusys 数据库的student表创建一个触发器T_deleteStudentScore，当删除表student中某个学生的记录时，同时将score表中与该学生有关的数据全部删除。#在stusys 数据库中score表创建触发器T_insertScoreRecord,当向score时，显示'正在插入记录'#[8]创建事件E_startDays，从第2天起，每天清空表tb，在2021年12月31日结束。#【例11.6】创建现在立即执行的事件E_direct，执行时创建一个表tb。
MySQL数据库教程之十四：MySQL触发器
fjqlldg的专栏
04-18 4065
MySQL数据库教程之十四：MySQL触发器 准备工作： 先启动Navicat for MySQL,并打开前期建立的数据库和表 查看每个表的结构： 一、触发器概念 触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件...
【学习数据库】触发器
和我一起学习
05-09 1032
创建、修改、删除、inserted表、deleted表
MySQL触发器
南宫乘风-Linux运维-虚拟化容器-Python编程 ownit.top
04-18 1532
MySQL触发器Triggers ======================================================== 触发器简介 创建触发器 查看触发器 删除触发器 触发器案例 一、触发器简介 触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发， 比如当对一个表进行操作（ insert，delete， upd...
mysql触发器删除表数据时将删除的数据插入到备份表中
qq_36677358的博客
10-25 5177
使用触发器 CREATE TRIGGER '触发器名称' BEFORE DELETE ON '删除的主表名称' FOR EACH ROW BEGIN INSERT INTO '备份表名称' SELECT * FROM '主表名称' where id=old.id; END
触发器（数据库实验你懂的）
qq_45687780的博客
11-16 3835
本博客是关于PL/SQL中oracle数据库的实验 实验环境 sqldevelop 实验前提：五个表 student course sc emp dept 实验一 1、创建一个行级UPDATE触发器，当更新课程表course中某门课程的学分时，激发触发器，输出该课程的课程号以及修改前的学分与修改后的学分。 2、创建一个行级DELETE触发器，当删除学生表student中某个学生信息时，激发触发器，同时删除选课表sc中该学生所有的选课信息。 3、创建一个带有限制条件的UPDATE触发器，修改
创建INSERT、DELETE、UPDATE 触发器示例 .
u010078133的专栏
03-13 7629
触发器是基于一个表创建的，但是可以针对多个表进行操作。所以触发器可以用来对表实施复杂的完整性的约束，当触发器所保存的数据发生改变时，触发器所保存的数据发生改变时，触发器被自动激活，从而防止对数据的不正确修改。         下面对每个INSERT、DELETE、UPDATE触发器举一个小例子来加深理解： INSERT触发器：         INSERT触发器就是当对定义触发器的表执行IN
SQL Server学习笔记：主键设置与SQL语句应用
"SQL Server学习总结.docx" 在学习SQL Server时，掌握其基本操作和常见问题的解决方法至关重要。这份文档详细总结了SQL Server 2012的一些关键点，包括界面、主键设置、SQL语句使用、表操作问题、数据库备份与还原...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值