SQL触发器实例(上)
时间：2021-07-01 10:21:17
帮助过：10人阅读
--1.) 创建测试用的表(testTable)
2 if exists (
select * from sysobjects
where name
=‘testTable‘)
3 drop table testTable
4 GO
5 Create Table testTable
6 (
7 testField
varchar(
50)
8 )
9
10 select * from testTable
11
12
13
14 --2.) 创建基于表(testTable)的触发器(testTrigger)
15 IF EXISTS (
Select name
FROM sysobjects
Where name
= ‘testTrigger‘ AND type
= ‘TR‘)
16 Drop TRIGGER testTrigger
17 GO
18 Create Trigger testTrigger
19 ON testTable
20 for Insert,
Delete,
Update
21 AS
22 if exists(
select * from inserted)
23 if exists(
select * from deleted)
24 print ‘...更新‘
25 else
26 print ‘...插入‘
27 else
28 if exists(
select * from deleted)
29 print ‘...删除‘
30 Go
31
32
33 --.) 操作testTable表,测试触发器testTrigger
34 --分别执行Insert Into语句,Update语句,Delete语句,看看效果
35 Insert Into testTable
values (
‘testContent!‘)
36
37 Update testTable
Set testField
= ‘UpdateContent‘
38
39 Delete From testTable
40
41 select * from testTable
42
43
44 --用到的功能有:
45 --1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
46 --2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录，等等。
47
48 --这时候可以用到触发器。对于需求1，创建一个Update触发器：
49
50 Create Trigger truStudent
51
52 On student
--在Student表中创建触发器
53 instead
of Update --为什么事件触发
54 As --事件触发后所要做的事情
55 if Update(stuID)
56 begin
57
58 Update borrow
59 Set stuID
=i.stuID
60 From borrow
as br , Deleted
as d ,Inserted
as i
--Deleted和Inserted临时表
61 Where br.stuID
=d.stuID
62
63 end
64
65 drop trigger truStudent
66 UPDATE student
set stuID
=‘1006‘ WHERE stuID
=‘1005‘
67
68
69 Create trigger trdStudent
70 On Student
71 instead
of Delete
72 As
73 Delete Borrow
74 From Borrow
as br , Deleted
as d
Where br.StuID
=d.stuID
75
76 drop trigger trdStudent
77 delete FROM student
WHERE stuID
=‘1004‘
78
79 disable
trigger trdStudent
on Student
80
81 select * from book
82 select * from borrow
83 select * from student
84
85
86 --创建触发器（对删除表的约束）
87 create trigger droptabel
88 on database
89 for drop_table
90 as
91 print‘删除表吗？‘
92 print‘不能删除表‘
93 rollback transaction
94 go
95
96 drop table students
97 disable
trigger droptabel
on database --关闭触发器
98 enable
trigger droptabel
on database
99
100
101
102
103
104 CREATE TABLE students
--学生信息表
105 (
106 stuID
CHAR(
10)
primary key,
--学生编号
107 stuName
CHAR(
10)
NOT NULL ,
--学生名称
108 major
CHAR(
50)
NOT NULL --专业
109 )
110 GO
111
112
113 CREATE TABLE borrowS
--借书表
114 (
115 borrowID
CHAR(
10)
primary key,
--借书编号
116 stuID
CHAR(
10)
NOT NULL,
--学生编号
117 BID
CHAR(
10)
NOT NULL,
--图书编号
118 T_time
datetime NOT NULL,
--借出日期
119 B_time
datetime --归还日期
120 )
121 GO
122
123 SElect * from students
124 select * from borrows
125
126 Create Trigger truStudents
127
128 On students
--在Student表中创建触发器
129 for Update --为什么事件触发
130 As --事件触发后所要做的事情
131 if Update(stuID)
132 begin
133
134 Update borrows
135 Set stuID
=i.stuID
136 From borrows
as br , Deleted
as d ,Inserted
as i
--Deleted和Inserted临时表
137 Where br.stuID
=d.stuID
138
139 end
140
141
142
143 UPDATE studentS
set stuID
=‘1006‘ WHERE stuID
=‘1005‘
144
145
146 Create trigger trStudent
147 On Students
148 for Delete
149 As
150 Delete borrows
151 From borrows
AS br , Deleted
AS d
152 Where br.stuID
=d.stuID
153
154
155 drop trigger trStudent
156
157
158 delete from students
where stuID
=‘1001‘
SQL触发器实例(上)
标签：update row 创建 删除表 field font uname for val
