【SQL Server】数据库开发指南（九）详细讲解 MS-SQL 触发器的的创建、修改、应用与适用场景_百度小程序

文章目录

前言
一、什么是触发器

1.1 DML 触发器

1.1.1 Instead of 触发器
1.1.2 After 触发器
1.2 DDL 触发器
1.3 INSERTED 表与 DELETED 表
二、T-SQL语句创建触发器各示例

2.1 基本通用语法
2.2 创建 insert 类型触发器
2.3 创建 delete 类型触发器
2.4 创建 update 类型触发器
2.5 update 更新列级触发器
2.6 创建 instead of 触发器
三、修改触发器
四、启用、禁用触发器
五、查询创建的触发器信息

六、结合触发器操作日志示例

前言

本文深入探讨数据库触发器的各个方面，帮助读者更好地理解和应用触发器技术。我们将详细介绍触发器的概念和作用，并重点讨论 DML 触发器和 DDL 触发器。在 DML 触发器部分，我们将关注 Instead of 触发器和 After 触发器的用法，同时介绍 INSERTED 表和 DELETED 表的特点。

接下来，我们将提供丰富的示例，演示使用 T-SQL 语句创建不同类型的触发器，包括 insert、delete、update 类型和列级触发器。此外，我们还将讨论修改触发器的技巧、启用和禁用触发器的方法，以及查询创建的触发器信息。通过结合实际案例，我们将帮助读者更好地理解触发器的应用场景和优化策略。无论您是初学者还是有一定经验的开发者，本文都将为您提供实用的触发器知识和技巧，助您在数据库操作中更加灵活和高效。

一、什么是触发器

触发器（Trigger）是一种特殊的代码段或操作，它会在特定事件发生时自动执行。触发器通常与数据库管理系统（DBMS）相关联，用于在数据库中定义和执行自动化操作。

触发器可以根据定义的条件和事件，触发预定的动作或响应。当满足触发器定义的条件时，触发器会自动触发，并执行与之关联的代码逻辑。触发器通常用于实现数据完整性约束、自动化数据更新和审计跟踪等功能。

SQL Server 中触发器可以分为两类：DML 触发器和DDL 触发器。

【SQL Server】数据库开发指南（九）详细讲解 MS-SQL 触发器的的创建、修改、应用与适用场景,在这里插入图片描述,第1张

1.1 DML 触发器

DML触发器（Data Manipulation Language Trigger）：这种触发器与表的数据操作（插入、更新、删除）相关。当执行与表相关的 DML语句时（如：INSERT、UPDATE、DELETE），系统会自动调用执行与该表相关的 DML 触发器。这些触发器允许你在数据操作之前或之后执行自定义的逻辑。它们通常用于实现复杂的数据完整性约束、数据验证、审计跟踪等功能。

DML触发器分为： Instead of 触发器（之前触发）和 After 触发器（之后触发）。

1.1.1 Instead of 触发器

Instead of 触发器用于替代引起触发器执行的T-SQL语句。除表之外，Instead of 触发器也可以用于视图，用来扩展视图可以支持的更新操作。当在表上执行与触发器相关的操作（如插入、更新、删除）时，如果存在适当的 INSTEAD OF 触发器，系统将跳过原始操作，而是执行 INSTEAD OF 触发器中定义的逻辑。

INSTEAD OF 触发器常见的应用：

视图更新：当操作涉及到视图时，可以使用 INSTEAD OF 触发器来处理视图的更新逻辑。视图是虚拟的表，可能包含来自多个基本表的数据。通过定义 INSTEAD OF 触发器，您可以在对视图进行插入、更新或删除操作时，自定义处理数据的逻辑。

复杂约束处理：某些复杂的数据约束无法直接通过常规的数据库约束（如主键、外键、唯一性约束）来实现。在这种情况下，您可以使用 INSTEAD OF 触发器来处理和验证数据的约束逻辑。

自定义操作：INSTEAD OF 触发器还可以用于执行自定义操作，而不仅仅是数据操作。您可以在触发器中执行复杂的业务逻辑、数据转换、数据合并等操作。

1.1.2 After 触发器

After 触发器要求只有执行某一操作 insert、update、delete 之后触发器才被触发，且只能定义在表上。After 触发器通常用于在数据操作完成后执行后续的操作或触发其他事件。

这里根据个人经验，总结几个 After 触发器的要点：

触发时机：After 触发器在执行与其关联的数据操作（如插入、更新、删除）之后触发。在执行原始操作后，触发器允许您在处理完成的数据上执行进一步的操作。

访问被触发表的数据：After 触发器可以通过使用特殊表 INSERTED 和 DELETED 来访问触发操作后的数据。INSERTED 表存储已插入或已更新的新数据，而 DELETED 表存储已更新或已删除的旧数据（后面会介绍）。

多个触发器的顺序：当多个 After 触发器与同一数据操作相关联时，它们将按照创建顺序依次触发。您可以使用 sp_settriggerorder 存储过程来更改 After 触发器的执行顺序。

功能和应用：After 触发器可用于执行各种后续操作，例如：

更新其他相关表中的数据，以确保数据的一致性。
生成日志记录或审计跟踪信息，以便记录操作历史或进行审计。
触发其他触发器或存储过程，以便执行进一步的业务逻辑。

发送通知或触发事件，以便在操作完成后通知相关方。

注意：After 触发器的复杂性和执行频率可能会对数据库性能产生影响。因此，在创建 After 触发器时，建议遵循最佳实践，并进行性能测试和调优以确保系统性能不受负面影响。

1.2 DDL 触发器

DDL 触发器（Data Definition Language Trigger）：这种触发器与数据库对象的定义操作（创建、修改、删除）相关。当执行与数据库对象相关的 DDL 语句时（如CREATE、ALTER、DROP），系统会自动调用执行与该对象相关的 DDL 触发器。DDL 触发器通常用于在对象定义发生变化时执行某些操作，如记录变更历史、限制数据库架构更改等。

触发器与普通的存储过程的区别在于触发器是与特定表相关联的，它们在特定的数据操作事件发生时自动触发执行，而不需要显式地调用。触发器提供了一种在数据库层面上实现业务逻辑和数据约束的机制，可以在数据操作发生时进行验证和处理。

1.3 INSERTED 表与 DELETED 表

触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是删除的数据。

DELETED 表存放由于执行 delete 或 update 语句（存放更新前的记录）而要从表中删除的所有行。

INSERTED 表存放由于执行 insert 或 update 语句（存放更新后的记录）而要向表中插入的所有行。

注意：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

对表的操作	INSERTED 逻辑表	DELETED 逻辑表
增加记录（insert）	存放增加的记录	无
删除记录（delete）	无	存放被删除的记录
修改记录（update）	存放更新后的记录	存放更新前的记录

二、T-SQL语句创建触发器各示例

2.1 基本通用语法

语法结构如下：

create trigger trigger_name
　　on {table_name | view_name}
　　{for | After | Instead of }
　　[ insert, update,delete ]
　　as
　　sql_statement

2.2 创建 insert 类型触发器

insert 触发器，会在 inserted 表中添加一条刚插入的记录。

--创建insert插入类型触发器
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(20), @temp int;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = name from inserted;
    set @name = @name + convert(varchar, @id);
    set @temp = @id / 2;    
    insert into student values(@name, 18 + @id, @temp, @id);
    print '添加学生成功！';
go
--插入数据
insert into classes values('5班', getDate());
--查询数据
select * from classes;
select * from student order by id;

2.3 创建 delete 类型触发器

delete触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。

--delete删除类型触发器
if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on classes
    for delete --删除触发
as
    print '备份数据中……';    
    if (object_id('classesBackup', 'U') is not null)
        --存在classesBackup，直接插入数据
        insert into classesBackup select name, createDate from deleted;
    else
        --不存在classesBackup创建再插入
        select * into classesBackup from deleted;
    print '备份数据成功！';
go
--
--不显示影响行数
--set nocount on;
delete classes where name = '5班';
--查询数据
select * from classes;
select * from classesBackup;

2.4 创建 update 类型触发器

update 触发器会在更新数据后，将更新前的数据保存在 deleted 表中，更新后的数据保存在 inserted 表中。

--update更新类型触发器
if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更新前的数据
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            --更新后的数据
            select @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
            print '级联修改数据成功！';
        end
    else
        print '无需修改student表！';
go
--查询数据
select * from student order by id;
select * from classes;
update classes set name = '五班' where name = '5班';

2.5 update 更新列级触发器

更新列级触发器可以用update是否判断更新列记录；

if (object_id('tgr_classes_update_column', 'TR') is not null)
    drop trigger tgr_classes_update_column
go
create trigger tgr_classes_update_column
on classes
    for update
as
    --列级触发器：是否更新了班级创建时间
    if (update(createDate))
    begin
        raisError('系统提示：班级创建时间不能修改！', 16, 11);
        rollback tran;
    end
go
--测试
select * from student order by id;
select * from classes;
update classes set createDate = getDate() where id = 3;
update classes set name = '四班' where id = 7;

2.6 创建 instead of 触发器

if (object_id('tgr_classes_inteadOf', 'TR') is not null)
    drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_inteadOf
on classes
    instead of delete/*, update, insert*/
as
    declare @id int, @name varchar(20);
    --查询被删除的信息，病赋值
    select @id = id, @name = name from deleted;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
    --先删除student的信息
    delete student where cid = @id;
    --再删除classes的信息
    delete classes where id = @id;
    print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;

三、修改触发器

alter trigger tgr_message
on student
after delete
as raisError('tgr_message 触发器被触发', 18, 12);
go
--test
delete from student where name = 'xiaoming';

四、启用、禁用触发器

--禁用触发器
disable trigger tgr_message on student;
--启用触发器
enable trigger tgr_message on student;

五、查询创建的触发器信息

--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';
--查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';
--查看创建触发器语句
exec sp_helptext 'tgr_message';

六、结合触发器操作日志示例

下面进行一个举例，创建一个名为 log 的表和一个名为 tgr_student_log 的触发器。触发器用于在对 student 表进行插入、更新、删除操作时记录对应的动作到 log 表中。根据插入和删除操作是否存在，触发器分别插入不同的动作类型到 log 表中。最后，通过插入、更新、删除 student 表的操作进行测试，并查看 log 表和 student 表的结果。

if (object_id('log', 'U') is not null)
    drop table log
go
create table log(
    id int identity(1, 1) primary key,
    action varchar(20),
    createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where name = 'tgr_student_log'))
    drop trigger tgr_student_log
go
create trigger tgr_student_log
on student
after insert, update, delete
as
    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
    begin
        insert into log(action) values('updated');
    end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log(action) values('inserted');
    end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
        insert into log(action) values('deleted');
    end
go
--test
insert into student values('xiaoming', 23, 6, 7);
update student set sex = 0 where name = 'xiaoming';
delete student where name = 'xiaoming';
select * from log;
select * from student order by id;

	[ 本文作者 ]   bluetata
	[ 原文链接 ]   https://bluetata.blog.csdn.net/article/details/131093456
	[ 最后更新 ]   06/07/2023 19:58
	[ 版权声明 ]   如果您在非 CSDN 网站内看到这一行，
	说明网络爬虫可能在本人还没有完整发布的时候就抓走了我的文章，
	可能导致内容不完整，请去上述的原文链接查看原文。

做网站平台的公司有哪些设计公司手机网站网站设计的内容有哪些在线定制图片深圳公司转让流程海口网站建设介绍

上一篇：基于springboot的学生在线选课系统的设计与实现毕业设计开题报告

下一篇：初识Go语言1【Windows 上安装 Go 环境，以及GO111MODULE、GOPROXY环境配置、Go命令介绍】

文章目录

前言

一、什么是触发器

1.1 DML 触发器

1.1.1 Instead of 触发器

1.1.2 After 触发器

1.2 DDL 触发器

1.3 INSERTED 表 与 DELETED 表

二、T-SQL语句创建触发器各示例

2.1 基本通用语法

2.2 创建 insert 类型触发器

2.3 创建 delete 类型触发器

2.4 创建 update 类型触发器

2.5 update 更新列级触发器

2.6 创建 instead of 触发器

三、修改触发器

四、启用、禁用触发器

五、查询创建的触发器信息

六、结合触发器操作日志示例

1.3 INSERTED 表与 DELETED 表