-- 一、触发器：
-- 1在每次插入数据的时候都把插入的数据值打印出来。
use MySchool
select * from Class
create trigger trigger_Class_insert
on Class after insert
as
begin
declare @clsid int
declare @cName varchar(50)
declare @cDesc varchar(50)
select @clsid=clsId,@cName=cName,@cDesc=cDescription from inserted
print @clsid
print @cName
print @cDesc
end
insert into Class values ('微机班','好班2')
-- 2将删除的表中（Class）的数据备份到另外一个表中（ClassBak）
-- (1)建一个空表（ClassBak）与Class的结构一样。
select top 0 * into ClassBak from Class
-- (2)创建触发器
create trigger trigger_Class_Delete
on Class after delete
as
begin
set identity_insert ClassBak on
insert into ClassBak (clsId,cName,cDescription)
select * from deleted
set identity_insert ClassBak off
end
delete from Class
select * from Class
select * from ClassBak
-- 3--替换触发器(instead of)--替换删除操作
create trigger trigger_ClassBak_Instead_of
on Class instead of delete
as
begin
set identity_insert ClassBak on
insert into ClassBak (clsId,cName,cDescription)
select * from deleted
set identity_insert ClassBak off
end
delete from Class
drop trigger trigger_ClassBak_Instead_of
-- 二、游标
--1讲的工资更新，更新后的金额为原来的工资+奖金。
create table TblTeacherSalary
(
autoId int identity(1,1) primary key,
tTId int not null,
reward money
)
insert into TblTeacherSalary values(1,1200)
insert into TblTeacherSalary values(2,2100)
insert into TblTeacherSalary values(3,800)
insert into TblTeacherSalary values(4,200)
select * into TblTeacher from teacher
select * from TblTeacher
select * from TblTeacherSalary
declare @id int
declare @Salary money
declare cur_TblTeacherSalary cursor fast_forward
for select tTId,reward from TblTeacherSalary
open cur_TblTeacherSalary
fetch next from cur_TblTeacherSalary into @id,@Salary
while @@FETCH_STATUS=0
begin
update TblTeacher set tSalary+=@Salary where tTId=@id
fetch next from cur_TblTeacherSalary into @id,@Salary
end
close cur_TblTeacherSalary
deallocate cur_TblTeacherSalary
-- 2将奖金中的reward设置为 工资的0.1倍
select * from TblTeacher
select * from TblTeacherSalary
declare @id1 int
declare @Salary1 money
declare cur_TblTeacherSalary cursor forward_only
for select tTId from TblTeacherSalary
open cur_TblTeacherSalary
fetch next from cur_TblTeacherSalary into @id1
while @@FETCH_STATUS=0
begin
select @Salary1=tSalary from TblTeacher where tTId=@id1
update TblTeacherSalary set reward=@Salary1*0.1 where CURRENT of
cur_TblTeacherSalary
fetch next from cur_TblTeacherSalary into @id1
end
close cur_TblTeacherSalary
deallocate cur_TblTeacherSalary
-- 3把游标写在对面的表上第一题把游标写在TblTeacher.改TblTeacher；
select * from TblTeacher
select * from TblTeacherSalary
declare @id2 int
declare @Salary2 money
declare cur_TblTeacher cursor forward_only
for select tTId from TblTeacher
open cur_TblTeacher
fetch next from cur_TblTeacher into @id2
while @@FETCH_STATUS=0
begin
select @Salary2=reward from TblTeacherSalary where tTId=@id2
update TblTeacher set tSalary+=@Salary2 where current of cur_TblTeacher
fetch next from cur_TblTeacher into @id2
end
close cur_TblTeacher
deallocate cur_TblTeacher
--第二题改TblTeacherSalary,我们把游标写在techar上。
select * from TblTeacher
select * from TblTeacherSalary
declare @id3 int
declare @Salary3 money
declare cur_TblTeacher cursor fast_forward
for select tTId,tSalary from TblTeacher
open cur_TblTeacher
fetch next from cur_TblTeacher into @id3,@Salary3
while @@FETCH_STATUS=0
begin
update TblTeacherSalary set reward=@Salary3*0.1 where tTId=@id3
fetch next from cur_TblTeacher into @id3,@Salary3
end
close cur_TblTeacher
deallocate cur_TblTeacher
-- 4把奖金表中的奖金更新为 reward-teacher.salary*0.05
select * from TblTeacher
select * from TblTeacherSalary
declare @id4 int
declare @Salary4 money
declare cur_TblTeacher cursor fast_forward
for select tTId,tSalary from TblTeacher
open cur_TblTeacher
fetch next from cur_TblTeacher into @id4,@Salary4
while @@FETCH_STATUS=0
begin
update TblTeacherSalary set reward-=@Salary4*0.05
fetch next from cur_TblTeacher into @id4,@Salary4
end
close cur_TblTeacher
deallocate cur_TblTeacher