专栏首页技术碎碎念sql server 触发器

sql server 触发器

触发器是一种特殊类型的存储过程。触发器可包含复杂的T-SQL语句。触发器不能通过名称被直接调用,也不允许设置参数。它是建立在触发事件上的。

触发器可以强制执行一定的业务规则,以保持数据完整性、检查数据有效性、实现数据库管理任务和一些附加功能。

触发器的分类: DML、 DDL、 登录触发器

创建触发器需要指定的选项:

 1.触发器的名称。

 2.在其上定义触发器的表。

 3.触发器将何时激发。

 4.激活触发器的数据修改语句。

 5.执行触发操作的编程语句。

CREATE TRIGGER语句基本语法格式如下:

CREATE TRIGGER     触发器名称

ON {表名 | 视图名}

[with encryption]

{

   { {FOR | AFTER | INSTEAD OF}

   {[DELETE] [,][INSERT] [,] [UPDATE]}

   AS

   sql_statement […n ]

其中:

AFTER

指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器。如果仅指定 FOR 关键字,则 AFTER 是默认设置。

INSTEAD OF

指定执行触发器而不是执行触发 SQL 语句,从而替代触发语句的操作。

例如:

 1 /*在student表上创建触发器,
 2 在用户插入、修改和删除记录时,都会自动显示表中的内容:*/
 3 
 4 use test
 5 go
 6 
 7 create trigger trig_1 on student
 8 after insert,delete,update
 9 as 
10 begin
11     set nocount on
12     select * from student
13 end
14 
15 insert student(sno) values(5)
16 
17 delete student where sno=5
18 
19 
20 exec sp_helptext trig_1    --查看触发器内容 
21 exec sp_helptrigger student   --查看表上的触发器的属性
22 select * from sysobjects where xtype='TR'  --查看数据库中已有的触发器
23 
24 
25 drop trigger trig_1

inserted表和deleted表

触发器执行的时候,产生两个临时表:inserted表和deleted表。它们的结构和所在的表的结构相同,可使用这两个表测试某些数据修改的效果和设置触发器操作的条件,但不能对表中的数据进行更改。

deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行delete或update语句时,行从触发器表中删除,并传输到deleted表中。

inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在插入和更新时,新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

在对具有触发器的表(触发器表)进行操作时,有:

执行INSERT操作,插入到触发器表中的新行被插入到inserted表中。

执行DELETE操作,从触发器表中删除的行被插入到deleted表中。

执行UPDATE操作,先从触发器表中删除旧行,然后再插入新行。删除的旧行插入到deleted表中;更改后的新行被插入到inserted 表中。

使用DML触发器

1. INSERT和UPDATE触发器

     当向表中插入或者更新记录时,INSERT或者UPDATE触发器被激活。一般情况下,这两种触发器常用来检查插入或者修改后的数据是否满足要求。 

INSERT触发器被触发时,新的记录增加到触发器的对应表中,并且同时也添加到一个inserted表中。

修改一个记录等于插入了一个新的记录并且删除一个旧的记录。当在一个有UPDATE触发器的表中修改记录时,表中原来的记录被移动到deleted表中,修改过的记录插入到了插入表中,触发器可以参考deleted表和inserted表以及被修改的表,以确定如何完成数据库操作。

2. DELETE触发器

DELETE触发器通常用于下面的情况:

防止那些确实要删除,但是可能会引起数据一致性问题的情况,一般是用于那些用作其他表的外部键记录。

用于级联删除操作。

例如:

 1 /*例:下例说明inserted表和deleted表的作用*/
 2 
 3 if exists(select name from sysobjects where name='trig_2' and type='TR')
 4     drop trigger trig_2
 5 go
 6 
 7 create trigger trig_2 
 8 on student
 9 after update                         --update触发器
10 as 
11     print 'inserted表'
12     select * from inserted
13     print 'deleted表'
14     select * from deleted
15 go
16 set nocount on
17 update student set sname='关二' where sno=2
18 --drop trigger trig_2
19 
20 
21 create trigger trig_3 
22 on student
23 after insert                         --insert触发器
24 as 
25     print 'inserted表'
26     select * from inserted
27     print 'deleted表'
28     select * from deleted
29 go
30 insert student values(100,'刘一百','男',25)
31 drop trigger trig_3
32 
33 
34 create trigger trig_4 
35 on student
36 after delete                         --delete触发器
37 as 
38     print 'inserted表'
39     select * from inserted
40     print 'deleted表'
41     select * from deleted
42 go
43 delete student where sno=100
44 drop trigger trig_4

修改触发器

语法格式:

ALTER TRIGGER trigger_name

ON ( table | view )

{

  { ( FOR | AFTER | INSTEAD OF ) }

  { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

AS

  sql_statement […n ]

  }   

DDL触发器使用

例如:

 1 /*DDL触发器*/
 2 
 3 /*在test数据库上创建一个DDL触发器safe,
 4 用来防止数据库中的任一表被修改或删除。*/
 5 
 6 create trigger safetest
 7 on database                         --数据库DDL触发器
 8 after drop_table,alter_table
 9 as
10 begin
11     raiserror('不能修改表结构',16,2)
12     rollback
13 end
14 go
15  
16 --执行以下程序,观察结果
17 alter table student add nation char(10)
18 
19 disable trigger safetest on database
20 drop trigger safetest on database
21 ---------------------------------------------------------
22 
23 
24 /*在服务器上创建一个DDL触发器tablecreat,
25 用来防止在服务器上创建数据库*/
26 
27 create trigger trig_last
28 on all server
29 after create_database
30 as
31 begin
32     raiserror('不能创建新的数据库',16,2)
33     rollback
34 end
35 go
36 
37 --执行以下程序,观察结果
38 create database test_trig
39 
40 disable trigger trig_last on all server
41 drop trigger trig_last on all server

删除触发器

使用SQL Server Management Studio删除触发器

使用DROP TRIGGER语句来删除触发器。其语法格式如下:

DROP TRIGGER { trigger } [ , …n ]

触发器禁用和启用

例如:

 1 /*触发器禁用和启用*/
 2 
 3 /*禁用sc表上的触发器trig_g。*/
 4 alter table sc disable trigger trig_g
 5 disable trigger trig_g on sc
 6 go
 7 
 8 /*启用sc表上的触发器trig_g。*/
 9 alter table sc enable trigger trig_g
10 enable trigger trig_g on sc
11 go
12 
13 --执行以下程序,观察结果
14 insert into sc values(1,100,1,-50)
15 select * from sc

触发器具体应用

例如:

 1 /*具体应用*/
 2 
 3 /*创建触发器trig3,
 4 当删除student表中的学生记录时,
 5 应该同时删除sc表中对应的记录*/
 6 
 7 create trigger trig_5
 8 on student
 9 for delete
10 as 
11  delete sc
12  where sc.sno in(select sno from deleted)
13 go
14 
15 select * from student
16 select * from sc
17 go
18 
19 /*exec sp_help score   --查看其中外键
20 
21 alter table score         --删除外键
22 drop CONSTRAINT FK_score_course
23 
24 alter table score
25 drop CONSTRAINT FK_score_student*/
26 
27 
28 delete student where sno=3
29 go
30 
31 select * from student
32 select * from sc 
33 -------------------------------------------------------------
34 
35 
36 create trigger trig_g 
37 on sc
38 after insert,update
39 as
40 begin
41     declare @g int
42     select @g=grade from inserted
43     if @g<0
44     begin
45          select '成绩必须>=0'
46          rollback
47     end
48 end
49 go
50 
51 --执行以下程序,观察结果
52 insert into sc values(1,10,1,50)
53 select * from sc
54 
55 
56 /*例: 建立一个修改触发器trigno,
57 该触发器防止用户修改表student的学号*/
58 
59 create trigger trigno
60 on student
61 after update
62 as
63 if update(sno)
64     begin
65         raiserror('不能修改学号',16,2)
66         rollback
67     end
68 go
69 
70 --执行以下程序,观察结果
71 update student set sno='2' where sno='1'
72 select * from student
73 ------------------------------------------------------
74 
75 
76 /*INSTEAD OF触发器*/
77 
78 /*例:在student表上创建一个INSTEAD OF触发器trig_6,
79 当用户插入数据时注意观察触发器的执行。*/
80 
81 create trigger trig_6
82 on student
83 instead of insert
84 as
85     select * from student
86 go
87 
88 --执行以下程序,观察结果
89 insert into student(sno,sname) values('300','白扯')

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • c++实现简单计算器

    帮一个同学写的,非计算机类专业,应付交差,也没什么功能,两个数的加减乘除运算,以及三角函数的运算。要求用到模板、运算符重载和异常处理。 一直以来都是用的java...

    欠扁的小篮子
  • windows API 开发飞机订票系统 图形化界面 (二)

    首先,用到的数据结构的定义、以及全局变量和函数的声明如下: 1 // Flight.c : 定义应用程序的入口点。 2 // 3 #include ...

    欠扁的小篮子
  • LeetCode-70-Climbing Stairs

    You are climbing a stair case. It takes n steps to reach to the top. Each time y...

    欠扁的小篮子
  • 触发器最全总结(适合复习数字逻辑使用)

    cuptobjut
  • Mysql触发器

    MySQL触发器 1、 触发器作用 日志跟踪:使用数据库记录apache等日志,可以使用自动插入操作 验证数据:触发器可以验证更新数据库要存入的数据 引用完整:...

    苦咖啡
  • Oracle触发器

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    颍川
  • 触发器在渗透中的利用

    0x01 什么是触发器: 触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的...

    逸鹏
  • MySQL触发器

    爱撒谎的男孩
  • MySQL从删库到跑路_高级(五)——触发器

    触发器是和表关联的特殊的存储过程,可以在插入,删除或修改表中的数据时触发执行,比数据库本身标准的功能有更精细和更复杂的数据控制能力。

    良月柒
  • 0642-6.2-如何在CM界面创建触发器

    Fayson在这里先介绍下CM中的trigger,也就是触发器。触发器是当一个或多个特定条件得到满足的服务、角色、角色组、或主机将采取指定动作的声明。条件为ts...

    Fayson

扫码关注云+社区

领取腾讯云代金券