create database TestTriggerDb
go
use TestTriggerDb
go
create table Student
(
StuId char(4) primary key,
StuName varchar(10)
)
go
create table Score
(
StuId char(4),
WrittenExam numeric(5,2),
LabExam numeric(5,2)
)
insert student values('1101','张1')
insert student values('1102','张2')
insert student values('1103','张3')
insert student values('1104','张4')
insert Score values('1101',rand()*101,rand()*101)
insert Score values('1102',rand()*101,rand()*101)
insert Score values('1103',rand()*101,rand()*101)
insert Score values('1104',rand()*101,rand()*101)
select * from student
select * from score
------------------
--触发器可以分两种：
--1，after trigger 2， instead of trigger
--区别是after trigger是实际操作完成后触发，
--而instead of是实际操作前触发，并且会替换实际操作，
--也就是说触发instead of 之后实际操作将不会再执行
------------------
--创建触发器
if exists(select * from sysobjects where name=N'trigger_Stu_stuId')
drop trigger trigger_Stu_stuId
go
create trigger trigger_Stu_stuId
on student
instead of update,delete
as
if update(StuId)
begin
update Score set score.StuId=i.StuId
from Inserted i,Deleted d
where score.StuId=d.StuId
end
print 'trigger'
go
--test sql:
update student set stuId=1105 where stuId=1101
--update student set stuId=1101 where stuId=1105
select * from student
select * from score
--结果如下：
StuId StuName
----- ----------
1101 张1
1102 张2
1103 张3
1104 张4
(4 行受影响)
StuId WrittenExam LabExam
----- --------------------------------------- ---------------------------------------
1105 74.43 78.52
1102 52.48 42.06
1103 95.48 43.14
1104 86.82 12.85
1105 94.87 25.20
1102 91.43 4.74
1103 29.82 84.51
1104 42.66 27.91
1105 31.53 66.85
1102 32.63 64.26
1103 81.22 64.88
1104 76.14 80.34
1105 1.82 58.37
1102 51.98 73.34
1103 61.74 77.99
1104 91.15 75.50
--从结果可以看出，触发器已经成功更新了score 表，但student表的更新没有执行，
--主要是被instead of 触发器替换了
--接下来再把表恢复到原来状态
update score set stuId=1101 where stuId=1105
select * from student
select * from score
--结果如下，部分省略
StuId StuName
----- ----------
1101 张1
1102 张2
1103 张3
1104 张4
(4 行受影响)
StuId WrittenExam LabExam
----- --------------------------------------- ---------------------------------------
1101 74.43 78.52
1102 52.48 42.06
1103 95.48 43.14
1104 86.82 12.85
1101 94.87 25.20
--
--创建after触发器
if exists(select * from sysobjects where name=N'trigger_Stu_stuId')
drop trigger trigger_Stu_stuId
go
create trigger trigger_Stu_stuId
on student
after update,delete--在这里指定触发器类型和触发条件
as
if update(StuId)
begin
update Score set score.StuId=i.StuId
from Inserted i,Deleted d
where score.StuId=d.StuId
end
print 'trigger'
go
--test sql:
update student set stuId=1105 where stuId=1101
select * from student
select * from score
--结果如下：
StuId StuName
----- ----------
1102 张2
1103 张3
1104 张4
1105 张1
(4 行受影响)
StuId WrittenExam LabExam
----- --------------------------------------- ---------------------------------------
1105 74.43 78.52
1102 52.48 42.06
1103 95.48 43.14
1104 86.82 12.85
1105 94.87 25.20
--从结果可以看出，两个表都更新成功