1 --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'