SQL触发器

小语雀网

发布于 2021-09-08 15:01:59

1.1K0

发布于 2021-09-08 15:01:59

文章被收录于专栏：小语雀网

什么是触发器？ 触发器是一种用来保障参照完整性的特殊的存储过程，它维护不同表中数据间关系的有关规则。当对指定的表进行某种特定操作（如：Insert,Delete或Update）时，触发器产生作用。触发器可以调用存储过程。

常见的触发器有三种：分别应用于Insert,Update,Delete事件。

触发器的限制：

1、一个表最多只能有三个触发器，insert,update,delete 2、每个触发器只能用于一个表 3、不能对视图、临时表创建触发器 4、Truncate table能删除表，但不能触发触发器 5、不能将触发器用于系统表

使用触发器的优点 1、触发器是自动的：它们在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 2、触发器可以通过数据库中的相关表进行层叠更改。例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其它表中的各匹配行采取删除操作。该触发器用 title_id 列作为唯一键，在 titleauthor、sales 及 roysched 表中对各匹配行进行定位。 3、触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。与 CHECK 约束不同的是，触发器可以引用其它表中的列。例如，触发器可以回滚试图对价格低于 10 美元的书（存储在 titles 表中）应用折扣。

--删除触发器
drop trigger 触发器名称

--判断触发器是否存在
if (object_id('触发器名') is not null)
    --删除触发器
    drop trigger 触发器名
go

--创建触发器
if (object_id('触发器名') is not null)
    drop trigger 触发器名
go
Create Trigger 触发器名
On 表名
For insert/update/delete
As
Begin
SQL语句（块）
End

下面在数据表上进行操作触发器，首先创建一个数据表TestTable

CREATE TABLE TestTable(
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [nvarchar](50) NULL,
 [Subject] [nvarchar](50) NULL,
 [Source] [numeric](18, 0) NULL
) ON [PRIMARY]
go
INSERT INTO TestTable ([UserName],[Subject],[Source]) 
 SELECT N'张三',N'语文',60 UNION ALL
 SELECT N'李四',N'数学',70 UNION ALL
 SELECT N'王五',N'英语',80 UNION ALL
 SELECT N'王五',N'数学',75 UNION ALL
 SELECT N'王五',N'语文',57 UNION ALL
 SELECT N'李四',N'语文',80 UNION ALL
 SELECT N'张三',N'英语',100
GO

1、创建insert插入类型触发器，用于判断插入的姓名是否存在，存在则插入失败进行回滚，不存在则插入成功，

--判断触发器tgr_insert是否存在
if (object_id('tgr_insert') is not null)
    --删除触发器tgr_insert
    drop trigger tgr_insert
go
--创建触发器tgr_insert
create trigger tgr_insert
--TestTable表示需要操作的表名
on TestTable
    for insert --插入触发
as
--定义变量
declare
@UserName nchar(20),
@Subject nchar(20),
@Source int,
@ishave int;
--给变量赋值
select @UserName=UserName,@Subject=Subject,@Source=Source from TestTable
--查询姓名相同的行数赋值给@ishave
select @ishave=COUNT(*) from TestTable where UserName=@UserName
--如果行数大于1表示该姓名已存在
if(@ishave>1)
begin
--print提示信息
print('插入失败，事务已回滚')
--事务数据回滚
Rollback transaction    
end
else
--否则该姓名不存在，则插入成功
print('插入成功')

--插入数据进行测试,执行两次看输出结果
insert into TestTable values('张三三','天文',200);

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021-04-20 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql