首页 > 数据库 >数据库实验（一）SQL Server触发器

数据库实验（一）SQL Server触发器

时间：2024-03-26 18:00:13浏览次数：20  
标签：insert 触发器 -- sum Server score student SQL

目录

触发器的定义

触发器和存储过程的区别

触发器的优点

触发器的作用

触发器的分类

DML触发器

DDL触发器

登录触发器

触发器的工作原理

inserted表

deleted表

创建触发器

编程要求

测试要求： 

实验代码：

触发器的定义

触发器是建立在触发事件上的。例如，对表执行 INSERT 、 UPDATE 或 DELETE 等操作时， SQL Server 就会自动执行建立在这些操作上的触发器。在触发器中，包含了一系列用于定义业务规则的 SQL 语句，用来强制用户实现这些规则，从而确保数据的完整性。

触发器和存储过程的区别

触发器与存储过程的区别是运行方式的不同，触发器不能用 EXECUTE 语句调用，而是在用户执行 SQL 语句时，自动触发执行。而存储过程需要用户、应用程序或者触发器来显示地调用并执行。

触发器的优点

  • 触发器是自动的。当对表中的数据做了任何修改之后，立即被激活；

  • 触发器可以通过数据库中的相关表，进行层叠修改；

  • 触发器可以强制限制。这些限制比用 CHECK 约束所定义的更复杂，与 CHECK 约束不同的是，触发器可以引用其他表中的列。

触发器的作用

触发器的主要作用就是，其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提供比 CHECK 约束更复杂的数据完整性，并自定义错误消息。触发器的主要作用主要有以下几个方面：

  • 强制数据库间的引用完整性；

  • 级联修改数据库中所有相关的表，自动触发其它与之相关的操作；

  • 跟踪变化，撤销或回滚违法操作，防止非法修改数据；

  • 返回自定义的错误消息，约束无法返回信息，而触发器可以；

  • 触发器可以调用更多的存储过程。

触发器的分类

SQL Server 包括三种常规类型的触发器： DML 触发器、 DDL 触发器和登录触发器。

DML触发器

DML 触发器是一些附加在特定表或视图上的操作代码。当数据库服务器中，发生数据操作语言事件时，执行这些操作。 SQL Server 中的 DML 触发器有三种：

INSERT 触发器：向表中插入数据时被触发；

DELETE 触发器：从表中删除数据时被触发；

UPDATE 触发器：修改表中数据时被触发。

当遇到下列情形时，应考虑使用 DML 触发器：

  • 通过数据库中的相关表实现级联更改；

  • 防止恶意或者错误的 INSERT 、 DELETE 和 UPDATE 操作，并强制执行 CHECK 约束定义的限制更为复杂的其他限制；

  • 评估数据修改前后表的状态，并根据该差异采取措施。

DDL触发器

DDL 触发器是当服务器或者数据库中发生数据定义语言（主要是以 CREATE ， DROP ， ALTER 开头的语句）事件时，被激活使用。使用 DDL 触发器，可以防止对数据架构进行的某些更改，还可以防止对记录数据中的更改或事件操作。

登录触发器

登录触发器会为响应 LOGIN 事件而激发存储过程，与 SQL Server 实例建立用户会话时，将引发此事件。

登录触发器将在登录的身份验证阶段完成之后，且用户会话实际建立之前激发。因此，来自触发器内部，且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

触发器的工作原理

触发器触发时：

  • 系统自动在内存中创建 deleted 表或 inserted 表；

  • 只读，不允许修改，触发器执行完成后，自动删除。

inserted表

  • 临时保存插入或更新后的记录行；

  • 可以从 inserted 表中，检查插入的数据是否满足业务需求；

  • 如果不满足，则向用户报告错误消息，并回滚插入操作。

deleted表

  • 临时保存删除或更新前的记录行；

  • 可以从 deleted 表中，检查被删除的数据是否满足业务需求；

  • 如果不满足，则向用户报告错误消息，并回滚插入操作。

inserted 表和 deleted 表对照：

 

创建触发器

创建触发器语法示例如下：

CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
AS
BEGIN
SQL语句
END

我们以 INSERT 触发器的创建为例，讲解触发器的创建和使用。首先创建测试数据表：

--创建学生表
create table student(
stu_id int identity(1,1) primary key,
stu_name varchar(10),
stu_gender char(2),
stu_age int
)

为 student 表创建 INSERT 触发器：

--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
declare @stuNumber int;
select @stuNumber = count(*)from student;
if not exists (select * from student_sum)--判断表中是否有记录
insert into student_sum values(0);
update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

测试触发器：

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到
--student_sum表中，更新stuCount
--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);
select stuCount 学生总人数 from student_sum;

另外，因为定义学生总数表 student_sum ，是向 student 表中插入数据后，才计算的学生总数。所以，学生总数表应该禁止用户，向其中插入数据。

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end

编程要求

我们已经为你建好了数据库与数据表，并添加了相应的数据内容。 你只需：

  • 补全右侧代码片段中 create trigger_insert_score 下的 Begin-End 区域间的代码，向 score 表建立一个插入触发器。保证向 score 表中插入的学生信息的学号，必须在 student 表中存在；

  • 补全右侧代码片段 create trigger_delete_student 下的 Begin-End 区域间的代码，向 student 表插入删除触发器，实现 student 表和 score 表的级联删除；

  • 补全右侧代码片段 create trigger_protect_grade 下的 Begin-End 区域间的代码，向 score 表建立触发器，使 grade 列不能手工修改。

表 student 如下所示，其中 birthday 是 date 类型，其余字段均为 varchar 类型。

snosnamesexbirthdaydisciplineschool
1001Tommale2019-06-01computerinformation
1002Bobmale2019-06-01softwareinformation
1003Alicefemale2019-05-02computerinformation

表 score 如下所示，字段 grade 为 float 类型，其余均为 varchar 类型。

snocnograde
100110189
100110278
100210188
100210280

测试要求： 

实验代码：

USE studentdb
go
 
SET NOCOUNT ON 
go
--********** create trigger_insert_score  **********--
--********** Begin **********--
create trigger trigger_insert_score
on score 
after insert 
as 
begin
if not exists 
(select* 
from student 
where sno in(
    select sno 
    from inserted))
begin
rollback transaction
begin transaction
end
end
--********** End **********--
go
delete from score
go
insert into score values('1001','2001','89.5')
go
insert into score values('1002','2001','95')
go
insert into score values('1011','2001','88')
go
select * from score
go
 
 
--********** create trigger_delete_student  **********--
--********** Begin **********--
create trigger trigger_delete_student
on student 
for delete
as
begin
delete from score 
where sno in(
    select sno 
    from deleted) 
end
--********** End **********--
go
delete from student 
where sno='1001'
go
select * 
from student
go
 
--********** create trigger_protect_grade **********--
--********** Begin **********--
create trigger trigger_protect_grade
on score 
for update
as
begin
rollback transaction
begin transaction
end
--********** End **********--
go
delete from score where sno='1001'
go
select * from score
go
select * from student
go

标签：insert,触发器,--,sum,Server,score,student,SQL
From： https://blog.csdn.net/nyxdsb/article/details/137023227

相关文章

  • SQLServer一致性错误解决
    工作中碰到的问题： 执行存储过程，提示错误信息：“内部插叙处理器错误：查询处理器在执行过程中遇到意外错误”。 初步怀疑是SQLServer中表“HS_Data”出现一致性错误或分配错误等原因造成。于是先用DBCCCHECKTABLE检查表“HS_Data”（DBCCCHECKTABLE用来检查组成表或索引视图......
  • MySQL ERROR 1227 (42000)错误处理
    在还原数据库的时候，遇到以下报错：ERROR1227(42000)atline18:Accessdenied;youneed(atleastoneof)theSUPER,SYSTEM_VARIABLES_ADMINorSESSION_VARIABLES_ADMINprivilege(s)forthisoperation从错误信息看，似乎是用户权限不够。但是，实际上不是权限的问题。解......
  • MySQL基础复习
    MySQL基础复习目录目录MySQL基础复习目录基础概念概念图MySQL数据模型基础命令启动-停止MySQL服务MySQL自带的客户端连接SQLSQL通用语法SQL分类语法学习通用解释DDL-数据定义语言（数据库-表的定义操作）①DDL-数据库操作②DDL-表操作番外：MySQL支持的数据类型数值型字符串型日期......
  • java毕业设计小说阅读网（springboot+mysql+jdk1.8+meven）
    本系统（程序+源码）带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景：随着互联网技术的飞速发展，人们的阅读习惯也在逐渐改变。在线阅读逐渐成为人们获取文学作品的主要方式之一。小说阅读网站作为提供在线阅读服务的平台，因其......
  • java毕业设计小区物业管理系统（springboot+mysql+jdk1.8+meven）
    本系统（程序+源码）带文档lw万字以上 文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景：随着城市化进程的加快，现代小区物业管理逐渐呈现出规模化、复杂化的特点。高效、规范的物业管理服务对于提升居民生活品质和社区环境至关重要。传统的物业......
  • Mysql的Innodb引擎--一起学习吧之数据库
    MySQL的InnoDB引擎是MySQL数据库管理系统中的一个重要的存储引擎，它通过提供事务支持、行级锁定、外键约束、MVCC、缓存、索引优化、数据恢复以及分区和表空间等功能，为数据库提供了高性能、高可靠性和高扩展性的解决方案。这使得InnoDB成为大多数MySQL应用的首选存储引擎。一、......
  • 【IT老齐074】海量数据大页码MySQL查询
    【IT老齐074】海量数据大页码MySQL查询场景分页最后数据查询慢，添加索引，索引失效SELECT *FROM blog_browse_historyORDERBY create_time LIMIT500000, 10;查询优化利用索引覆盖特性查找第50000页的起始时间，基于索引快速定位，向后取10条数据SELECT *FROM......
  • MySQL超大分页处理，以及优化思路说明。
    MySQL超大分页处理：MySQL中分页处理的方式：在MySQL中进行分页查询通常会使用LIMIT和OFFSET关键字来实现，下面是一种常见的方法：sqlCopyCodeSELECT*FROMyour_tableLIMIToffset,limit;offset表示起始行数，从0开始计数，即第一行为0。limit表示每页返回的行数。......
  • Python数据库模块(sqlite3,SQLite3)
    一、sqlite命令创建数据库：在控制台sqlite3name.databases查看数据库.tables查看表格名databaseName.dump>dumpName将数据库存在文本文件dumpName中，恢复就用databaseName<dumpName;attachdatabase......
  • MySQL官方使用手册（自译）
    Chatper1OverviewOfMySQLDBMSwhatisMySQLMySQL,themostpopularOpenSourceSQLdatabasemanagementsystem,isdeveloped,distributed,andsupportedbyOracleCorporation.TheMySQLwebsite(http://www.mysql.com/)providesthelatestinformationa......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99