广告位联系
返回顶部
分享到

SQL SERVER触发器详解

MsSql 来源:互联网 作者:秩名 发布时间:2022-02-26 20:59:32 人浏览
摘要

触发器是一种特殊的存储过程,触发器主要是通过事件进行触发而被自动调用执行,而存储过程必须通过存储过程的名称被调用。 一、触发器的定义 触发器是在对表进行插入、更新或

触发器是一种特殊的存储过程,触发器主要是通过事件进行触发而被自动调用执行,而存储过程必须通过存储过程的名称被调用。

一、触发器的定义

触发器是在对表进行插入、更新或删除操作时自动执行的特殊存储过程。触发器通常用于强制业务规则,触发器是一种高级约束,可以定义比CHECK约束更为复杂的约束:可以执行复杂的SQL语句(if/while/case),可以引用其他表中的列。触发器定义在特定的表上,与表相关,自动触发执行,不能直接调用,是一个事务(可回滚)。

二、触发器分类

SQL SERVER中触发器可以分为两类:DML触发器和DDL触发器,DML触发器针对表,DDL触发器会影响多种数据定义语言语句而触发,这些语句有create、alter、drop语句。

DML触发器分为:

1、after触发器(之后触发)

  • a、insert触发器
  • b、update触发器
  • c、delete触发器

2、instead of触发器(之前触发)

after触发器要求只有执行某一操作(insert、update、delete)之后触发器才能被触发,且只能定义在表上。而instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身,其优先级高于触发语句的执行。

触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)

这两张表是逻辑表也是虚表,触发器触发时系统自动在内存中创建这两张表,不会存储在数据库中。这两张表都是只读的,不允许修改。这两张表的结果总是与被触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。inserted表临时保存了插入或更新后的记录行,可以从inserted表中检查插入的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚插入操作。deleted表临时保存了删除或更新前的记录行,可以从deleted表中检查被删除的数据是否满足业务需求,如果不满足,则向用户报告错误消息,并回滚删除操作。update数据的时候是先删除表记录,然后插入一条记录,在inserted和deleted表就都有update后的数据记录了。

  inserted表和deleted表存放的信息:

修改操作

inserted表

deleted表

增加(INSERT)记录

存放新增的记录

删除(DELETE)记录

存放被删除的记录

修改(UPDATE)记录

存放更新后的记录

存放更新前的记录

三、创建触发器

语法:

1

2

3

4

5

6

7

8

9

10

11

12

13

CREATE TRIGGER trigger_name

 

ON table_name

 

[WITH ENCRYPTION]

 

FOR [DELETE, INSERT, UPDATE]

 

AS

 

  T-SQL语句

 

GO

WITH ENCRYPTION表示加密触发器定义的SQL文本

DELETE, INSERT, UPDATE指定触发器的类型

1、创建insert类型的触发器

插入触发器

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

--GradeInfo表中插入一条数据,MyStudentInfo表中插入一条记录

IF (object_id('tr_insert','tr') is not null)

    drop trigger tr_insert

GO

CREATE trigger tr_insert

on GradeInfo

after insert --插入触发

as

 begin

   --定义变量

   declare @GradeId int

   --在inserted表中查询已经插入记录信息

   select @GradeId=id from INSERTED

   --MyStudentInfo表中插入数据

   insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)

   print '插入成功!'

 end

插入数据

1

insert INTO GradeInfo VALUES (11,'C++')

查询数据

1

select * from MyStudentInfo where GradeId=11

2、delete触发器

删除MyStudentInfo表中的数据,插入备份表

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

--删除MyStudentInfo表中的数据,插入备份表

 IF (object_id('tr_Delete','tr') is not null)

    drop TRIGGER tr_Delete

 GO

 CREATE trigger tr_Delete

 on MyStudentInfo

 for delete

 as

  begin

    print '正在备份数据......'

 IF (object_id('MyStudentInfo_Back','U') is not null)

 --存在表,直接插入数据

   insert INTO MyStudentInfo_Back SELECT * from DELETED

 else

   select * into MyStudentInfo_Back from DELETED

 PRINT '备份完成'

  end

删除前查询MyStudentInfo表数据

1

select * from MyStudentInfo

删除id=9的数据

1

delete FROM MyStudentInfo where Id=9

查询备份表数据

1

select * from MyStudentInfo_Back

3、update触发器

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

IF (object_id('tr_Update','tr') is not null)

    drop TRIGGER tr_Update

 GO

 CREATE trigger tr_Update

 on MyStudentInfo

 for update

 as

   begin

  --声明变量,存储更新前和更新后的姓名

  declare @OldName varchar(16),@NewName varchar(16)

  select @OldName=name from DELETED

  print '更新前姓名:'+@OldName

  select @NewName=name from INSERTED

  print '更新后姓名:'+@NewName

end

把张三更新为"张三测试"

1

update MyStudentInfo SET Name='张三测试' where Id=1

update更新列级触发器

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

--update更新列级触发器

  IF (object_id('tr_update_column','tr') is not null)

     drop TRIGGER tr_update_column

  GO

  CREATE trigger tr_update_column

  on GradeInfo

  for update

  as

    begin

   IF(update(id))

     begin

    print '系统提示:主键ID不能更新'

    rollback

  end

 end

更新id列

1

update GradeInfo SET Id=15 where Id=4

4、instead of触发器

instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身的内容,其优先级高于定义的SQL语句的执行

语法:

1

2

3

4

5

6

7

8

create trigger tgr_name

on table_name

with encryption

    instead of update...

as

    begin

  T-SQL

 end

创建instead of触发器

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

--创建instead of触发器

/*MyStudentInfo表里面插入数据之前,先判断GradeInfo表中是否有对应的班级ID,如果没有,不允许插入,如果存在,则插入 */

IF (object_id('tr_insteadOf','tr') is not null)

   drop TRIGGER tr_insteadOf

GO

CREATE trigger tr_insteadOf

on MyStudentInfo

instead of insert

as

  begin

     IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED))

      print '该班级存在,可以插入'

  else

    begin

      print '该班级不存在,不可以插入'

   rollback

    end

  end

测试1,插入不存在的班级id

1

insert INTO MyStudentInfo (GradeId) VALUES (15)

测试2,插入存在的班级id

1

insert INTO MyStudentInfo (GradeId) VALUES (5)

DDL触发器

1

2

3

4

5

6

7

  create trigger tr_DDL on database

  for DROP_TABLE,ALTER_TABLE

  as

    begin

      print '别想着删库!好好打你的代码'

   rollback --回滚

 end

测试删除表

1

drop TABLE MyStudentInfo

测试修改表结构

1

2

alter table MyStudentInfo

alter column Name varchar(32)

禁用DML触发器

1

disable trigger tr_insteadOf on MyStudentInfo

启用DML触发器

1

enable trigger tr_insteadOf on MyStudentInfo

禁用DDL触发器

1

disable trigger tr_DDL on database

启用DDL触发器

1

enable trigger tr_DDL on database


版权声明 : 本文内容来源于互联网或用户自行发布贡献,该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权, 违法违规的内容, 请发送邮件至2530232025#qq.cn(#换@)举报,一经查实,本站将立刻删除。
原文链接 : https://www.cnblogs.com/dotnet261010/p/5924238.html
相关文章
  • 本站所有内容来源于互联网或用户自行发布,本站仅提供信息存储空间服务,不拥有版权,不承担法律责任。如有侵犯您的权益,请您联系站长处理!
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计