触发器的概念：它是由事件驱动的，就像java中的监听，当某个事件发生了，就会做一些工作。
下面直接上干货，创建insert触发器、delete触发器、DDL触发器和如何查看触发器定义
1.创建三个表学生表、班级表、课程表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
create
database
student_score
GO
--在数据库中创建三个表学生表、班级表、课程表的结构
use student_score
GO
create
table
student
( stu_id
char
(8)
primary
key
,
stu_name
char
(10),
stu_sex
char
(2),
stu_birthday smalldatetime,
class_id
char
(6)
)
go
create
table
class
( class_id
char
(6)
primary
key
,
class_name
varchar
(30),
class_num
int
,
)
create
table
course
( course_id
char
(3)
primary
key
,
course_name
varchar
(30),
)
go
create
table
score
( stu_id
char
(8),
course_id
char
(3),
score
int
check
(score>=0
and
score<=100)
primary
key
(stu_id,course_id)
)
go
|
2.插入用例数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
--往表中插入数据(student,course,score)
insert
into
student
values
(
'0601001'
,
'李玉'
,
'女'
,
'1987-05-06'
,
'0601'
)
insert
into
student
values
(
'0601002'
,
'鲁敏'
,
'女'
,
'1988-06-28'
,
'0601'
)
insert
into
student
values
(
'0601003'
,
'李小路'
,
'女'
,
'1987-01-08'
,
'0601'
)
insert
into
student
values
(
'0601004'
,
'鲁斌'
,
'男'
,
'1988-04-21'
,
'0601'
)
insert
into
student
values
(
'0601005'
,
'王宁静'
,
'女'
,
'1986-05-29'
,
'0601'
)
insert
into
student
values
(
'0601006'
,
'张明明'
,
'男'
,
'1987-02-24'
,
'0601'
)
insert
into
student
values
(
'0601007'
,
'刘晓玲'
,
'女'
,
'1988-12-21'
,
'0601'
)
insert
into
student
values
(
'0601008'
,
'周晓'
,
'男'
,
'1986-04-27'
,
'0601'
)
insert
into
student
values
(
'0601009'
,
'易国梁'
,
'男'
,
'1985-11-26'
,
'0601'
)
insert
into
student
values
(
'0601010'
,
'季风'
,
'男'
,
'1986-09-21'
,
'0601'
)
insert
into
class
values
(
'0501'
,
'计算机办公应用'
, 40)
insert
into
class
values
(
'0502'
,
'网络构建'
, 43)
insert
into
class
values
(
'0503'
,
'图形图像'
, 48)
insert
into
class
values
(
'0601'
,
'可视化'
, 41)
insert
into
class
values
(
'0602'
,
'数据库'
, 38)
insert
into
class
values
(
'0603'
,
'网络管理'
, 45)
insert
into
class
values
(
'0604'
,
'多媒体'
, 40)
insert
into
class
values
(
'0701'
,
'计算机办公应用'
, 39)
insert
into
class
values
(
'0702'
,
'WEB应用'
, 38)
insert
into
class
values
(
'0703'
,
'网络构建'
, 40)
insert
into
course
values
(
'001'
,
'计算机应用基础'
)
insert
into
course
values
(
'002'
,
'关系数据基础'
)
insert
into
course
values
(
'003'
,
'程序设计基础'
)
insert
into
course
values
(
'004'
,
'数据结构'
)
insert
into
course
values
(
'005'
,
'网页设计'
)
insert
into
course
values
(
'006'
,
'网站设计'
)
insert
into
course
values
(
'007'
,
'SQL Server 2000关系数据库'
)
insert
into
course
values
(
'008'
,
'SQL Server 2000程序设计'
)
insert
into
course
values
(
'009'
,
'计算机网络'
)
insert
into
course
values
(
'010'
,
'Windows Server 配置'
)
insert
into
score
values
(
'0601001'
,
'001'
,78)
insert
into
score
values
(
'0601002'
,
'001'
,88)
insert
into
score
values
(
'0601003'
,
'001'
,65)
insert
into
score
values
(
'0601004'
,
'001'
,76)
insert
into
score
values
(
'0601005'
,
'001'
,56)
insert
into
score
values
(
'0601006'
,
'001'
,87)
insert
into
score
values
(
'0601007'
,
'001'
,67)
insert
into
score
values
(
'0601008'
,
'001'
,95)
insert
into
score
values
(
'0601009'
,
'001'
,98)
insert
into
score
values
(
'0601010'
,
'001'
,45)
insert
into
score
values
(
'0601001'
,
'002'
,48)
insert
into
score
values
(
'0601002'
,
'002'
,68)
insert
into
score
values
(
'0601003'
,
'002'
,95)
insert
into
score
values
(
'0601004'
,
'002'
,86)
insert
into
score
values
(
'0601005'
,
'002'
,76)
insert
into
score
values
(
'0601006'
,
'002'
,57)
insert
into
score
values
(
'0601007'
,
'002'
,77)
insert
into
score
values
(
'0601008'
,
'002'
,85)
insert
into
score
values
(
'0601009'
,
'002'
,98)
insert
into
score
values
(
'0601010'
,
'002'
,75)
insert
into
score
values
(
'0601001'
,
'003'
,88)
insert
into
score
values
(
'0601002'
,
'003'
,78)
insert
into
score
values
(
'0601003'
,
'003'
,65)
insert
into
score
values
(
'0601004'
,
'003'
,56)
insert
into
score
values
(
'0601005'
,
'003'
,96)
insert
into
score
values
(
'0601006'
,
'003'
,87)
insert
into
score
values
(
'0601007'
,
'003'
,77)
insert
into
score
values
(
'0601008'
,
'003'
,65)
insert
into
score
values
(
'0601009'
,
'003'
,98)
insert
into
score
values
(
'0601010'
,
'003'
,75)
insert
into
score
values
(
'0601001'
,
'004'
,74)
insert
into
score
values
(
'0601002'
,
'004'
,68)
insert
into
score
values
(
'0601003'
,
'004'
,95)
insert
into
score
values
(
'0601004'
,
'004'
,86)
insert
into
score
values
(
'0601005'
,
'004'
,76)
insert
into
score
values
(
'0601006'
,
'004'
,67)
insert
into
score
values
(
'0601007'
,
'004'
,77)
insert
into
score
values
(
'0601008'
,
'004'
,85)
insert
into
score
values
(
'0601009'
,
'004'
,98)
insert
into
score
values
(
'0601010'
,
'004'
,75)
insert
into
score
values
(
'0601001'
,
'005'
,74)
insert
into
score
values
(
'0601002'
,
'005'
,68)
insert
into
score
values
(
'0601005'
,
'005'
,76)
insert
into
score
values
(
'0601008'
,
'005'
,85)
insert
into
score
values
(
'0601009'
,
'005'
,98)
insert
into
score
values
(
'0601010'
,
'005'
,75)
insert
into
score
values
(
'0601002'
,
'006'
,88)
insert
into
score
values
(
'0601003'
,
'006'
,95)
insert
into
score
values
(
'0601006'
,
'006'
,77)
insert
into
score
values
(
'0601008'
,
'006'
,85)
insert
into
score
values
(
'0601010'
,
'006'
,55)
insert
into
score
values
(
'0601001'
,
'007'
,84)
insert
into
score
values
(
'0601002'
,
'007'
,68)
insert
into
score
values
(
'0601003'
,
'007'
,95)
insert
into
score
values
(
'0601004'
,
'008'
,86)
insert
into
score
values
(
'0601005'
,
'008'
,76)
insert
into
score
values
(
'0601006'
,
'008'
,67)
insert
into
score
values
(
'0601007'
,
'009'
,67)
insert
into
score
values
(
'0601008'
,
'009'
,85)
insert
into
score
values
(
'0601009'
,
'010'
,98)
insert
into
score
values
(
'0601010'
,
'010'
,75)
|
3.练习实例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
--1）在student上创建<strong>INSERT触发器</strong>stu_insert，要求在student表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新class表中的class_nun列。并测试触发器stu_insert。
create
trigger
stu_insert
on
student
for
insert
as
update
class
set
class_num=class_num + 1
where
class_id=(
select
class_id
from
inserted)
--测试
insert
into
student
values
(
'0602011'
,
'文'
,
'女'
,
'1986-09-21'
,
'0602'
)
--2）在student上创建<strong>DELETE触发器</strong>stu_delete，要求在student表中删除记录时，这个触发器都将更新class表中的class_nun列。并测试触发器stu_delete。
create
trigger
stu_delete
on
student
for
insert
as
update
class
set
class_num=class_num - 1
where
class_id = (
select
class_id
from
deleted)
--测试
delete
from
student
where
stu_id=
'0601001'
--3）查看触发器相关信息:使用系统存储过程<strong>sp_help，sp_helptext查看触发器</strong>相关信息。
exec
sp_help
exec
sp_help stu_insert
exec
sp_helptext stu_insert
--4）对于下列触发器：
create
trigger
stu_update
on
student
instead
of
update
as
print
'修改学生表'
drop
trigger
stu_update
执行语句
update
student
set
stu_id=
'0601003'
where
stu_name=
'鲁斌'
会怎么样？
--消息 2627，级别 14，状态 1，第 1 行
--违反了 PRIMARY KEY 约束 'PK__student__E53CAB217F60ED59'。不能在对象 'dbo.student' 中插入重复键。
--语句已终止。
--5）创建<strong>DDL触发器</strong>，在当前数据库中不允许删除或修改表
create
trigger
data
on
database
for
drop_table, alter_table
as
print
'不允许删除或修改表'
rollback
|
扫一扫
581
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
