如何分析SQL Server数据库触发器概念、原理及案例

概述

触发器是SQL Server向程序员和数据分析师提供的一种确保数据完整性的方法。与表事件相关的特殊存储过程。它的执行不是由程序调用或手动启动，而是由事件触发。当一个表被操作（插入、删除、更新）时，它的执行就会被激活。触发器通常用于强制执行数据完整性约束和业务规则。

1.触发器的优点

1.触发器是自动的。对表中的数据进行任何修改后，它会立即激活。

2.触发器可以通过级联修改gh 数据库中的相关表。

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

2.触发器的分类

SqlServer包括三种通用类型的触发器：DML触发器、DDL触发器和登录触发器。

1.DML（数据操作语言）触发器

DML触发器是附加到一个当数据库服务器中发生数据操作语言事件时执行操作的特定表或视图。 SqlServer中的DML触发器分为三种类型：

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

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

更新触发器：当表中数据被修改时触发。

遇到以下情况时，应该考虑使用DML触发器：

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

防止恶意或错误的插入、更新和删除操作，并强制执行比检查定义的更复杂的附加限制约束。

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

2.DDL（数据定义语言，Data Definition Language）触发器

当数据定义语言（主要是create开头的语句， drop、alter）事件发生在服务器或数据库中。使用 DDL 触发器来防止对数据架构进行某些更改或记录数据中的更改或事件操作。

3.登录触发器

登录触发器将触发存储过程以响应 LOGIN 事件。当与 SQL Server 实例建立用户会话时引发此事件。身份验证后将触发登录触发器登录的第 n 阶段已完成且在用户会话实际建立之前。因此，触发器内通常会到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）都会发送到 SQL Server 错误日志。如果身份验证失败，则不会触发登录触发器。

3.触发器的工作原理

【DELETE、INSERT、UPDATE】对应执行删除、插入、更新三个动作时触发。

执行insert时，可以获取插入的数据，使用select*from insert读取

执行delete时，可以获取删除前的数据，可以使用select*from delete读取

p>

执行update时，要获取更新前的数据，可以使用select*fromdeleted读取。要获取更新后的数据，可以使用select*from insert读取

4.创建触发器

1.语法r 创建触发器：

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR |之后 | INSTEAD OF [DELETE, INSERT, UPDATE] AS T-SQL 语句 GO --with 加密表示加密触发器定义的 sql 文本 --delete、insert、update 指定触发器类型

2.创建插入触发器

--创建插入触发器create trigger trig_insert on Student after insert as begin if object_id(N'student_sum',N'U') is null--判断student_sum是否table存在create table Student_sum(stuCount int default(0));--创建student_sum表，存储学生人数声明@stuNumber int;从学生中选择@stuNumber = count(*)； if not contains (select * from Student_sum)--判断表中是否有记录insert into Student_sum Values(0);更新student_sum设置stuCount =@stuNumber; --将更新后的学生总数插入student_sum表end --测试触发器trig_insert-->作用是向student中插入数据，同时级联到student_sum表中，并更新stuCount --因为是后触发，先插入数据后触发触发器trig_insert； insert into Student(stu_name,stu_gender,stu_age)values('吕布','男',30);从student_sum中选择stuCount学生总数； insert into Student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);从student_sum中选择stuCount学生总数； insert into Student(stu_name,stu_gender,stu_age)values('曹阿莫','男',40); select StuCount 从student_sum中选择学生总数；

3、创建删除触发器

当用户执行删除操作时，删除触发器将会激活，从而控制用户从数据库中删除数据记录的能力。触发删除触发器后，会将用户删除的记录添加到deleted表中，并添加原表的对应记录最终表已被删除，因此在已删除的表中查看已删除的记录。

--创建删除触发器在删除后对学生创建触发器trig_delete as begin 选择stu_id 作为删除的学号，stu_name Stu_gender,stu_age from 删除end; --执行delete语句，触发trig_delete触发器Object delete from school where Stu_id=1;

4.创建UPDATE触发器

当用户对指定表执行更新语句时，会调用更新触发器。此类触发器用于约束用户对数据的修改。 update触发器可以执行两种操作：将更新前的记录存储在deleted表中，将更新后的记录存储在inserted表中。

--创建更新触发器在更新后在学生上创建触发器 trig_update as begin 声明 @stuCount int;从学生中选择@stuCount=count(*)；更新student_sum设置stuCount =@stuCount;选择stu_id作为更新前学生号ber,stu_name as update before update from returned select Stu_id as update the school number,stu_name as Updated school name from insert end --创建完成，执行一条update语句，触发trig_update触发器 update Student set Stu_name='张飞' where Stu_id=2;

5.管理触发器

1.查看触发器

(1)。查看数据库中所有触发器

--查看数据库中所有触发器 use 数据库名 go select * from sysobjects where xtype='TR'

sysobjects 保存的是数据库对象，以及记录xtype为TR的为触发对象。在名称列中我们可以看到触发器名称。

(2).sp_helptext 查看触发器内容

use 数据库名称 go exec sp_helptext '触发器名称'

触发器将以表格的形式显示内容。

除了触发器之外，sp_helptext还可以显示规则的文本、默认值、未加密的存储过程、用户定义的函数等。和意见。

(3).sp_helptrigger用于查看触发器的属性

sp_helptrigger有两个参数：第一个参数是表名；第二个参数是表名。第二个参数是触发器类型，为char(6)类型，可以是INSERT、UPDATE或DELETE。如果省略，将显示指定表中所有类型触发器的属性。

--查看数据库中所有触发器 use 数据库名 go select * from sysobjects where xtype='TR'

use 数据库名 goexec sp_helptrigger tableName

<强>2。禁用启用触发器

禁用：alter table表名禁用触发器触发器名称

启用：alter table表名启用触发器触发器名称

如果有是多个触发器，触发器名称用逗号分隔。

如果将“触发器名称”替换为“ALL”，则表示禁用或启用表的所有触发器。

3.修改触发器

--修改触发器语法 ALTER TRIGGER trigger_nameON table_name [WITH ENCRYPTION ] FOR {[DELETE][,][INSERT][,][UPDATE]} AS sql_statement;

4.删除触发器

 --语法格式：DROP TRIGGER { trigger } [ ,...n ] 参数：trigger：要删除的触发器名称 n：占位符，表示可以删除多个触发器< /pre>
你读过上面的内容了吗？对你有帮助吗？如果您想了解更多相关知识或阅读更多相关文章，请关注行业资讯频道。感谢您的支持。