sqlsever ，触发器的练习



学生表，成绩表，课程表，班级表

--1、 删除xs表中birth字段的check约束，并建立insert类型的触发器，
select *from xs
--实现，如果插入的记录的birth字段不处在1980-1-1至1991-12-31之间，进行操作回滚，并提示错误信息。
alter table xs  drop constraint   CK_xs_birth
drop trigger Tri_name
go
create trigger Tri_name
on xs
 for insert
 as
 declare @birth datetime
 set @birth=(select xs_birth from inserted)
 if(@birth>='1980-1-1' and @birth<='1990-12-31')
    print @birth
  else
   rollback transaction
 raiserror('错误失败',10,1)
go
insert into xs values('037','刘s',1,'1990-12-1','重庆','004',1.33,23.4)
select *from xs
--2、在xs表上建立insert触发器，每次成功插入一条记录时，对class表中的xs_count字段值要+1。
select *from xs
go
create trigger taker
on xs for insert
as
if(update(xs_class))
declare @classID nchar(20)
set @classID=(select xs_class from inserted)
update class set xs_count=xs_count+1
 where class_id=@classID
go
drop trigger taker
insert into xs values('039','刘小',1,'1990-12-1','重庆','004',1.33,23.4)
select *from xs
select *from class
--3 、在xs表上建立delete触发器，每次成功删除一条记录时，对class表中的xs_count字段值要-1。
go
create trigger deletes
on xs for delete
as
if(update(xs_class))
declare @stu char(20)
set @stu=(select top 1 xs_class from deleted)
update class set xs_count=xs_count-1
 where class_id=@stu
go
drop trigger deletes
delete from xs where xs_id='009'
select *from class
select *from xs
--4 、取消cj表中的外键约束，改用cj表的insert触发器实现当
--cj表的kc_xs_id,id在xs表和kc表中找不到对应的xs_id,kc_id时，进行操作回滚，并提示错误信息。
alter table cj  drop constraint   FK_cj_xs
select * from xs
go
alter trigger T_cj_update
on cj for insert
as
begin
declare @xs_id char(6),@kc_id char(3)
set @xs_id=(select xs_id from inserted)
set @kc_id=(select kc_id from inserted)
if(@xs_id in(select xs_id from xs ) and @kc_id in(select kc_id from kc))
print '成功插入数据'
else
begin
 rollback
 raiserror('错误失败',10,1)
 end
 end
go

drop trigger
select * from cj
select * from xs
select * from kc
delete from cj where cj=1000
insert into cj values ('015','005',1000)
--5 、在kc表上建立update触发器，如果修改kc表的kc_id字段时，
--update操作回滚，并提示不能修改kc_id的错误信息。
go
create trigger t_update
on kc for update
as
begin
if(update(kc_id))
 rollback
 raiserror('无法修改',10,1)
 end
go
update kc set kc_id='001'  where kc_id='009'
select *from kc
--6 、在xs表上建立update触发器，如果修改xs表的xs_id字段时，
--cj表中对应的xs_id也进行相应的修改。
go
create trigger t_update_xs
on xs for update
as
begin
declare @xs_id char(10)
declare @new_id char(10)
set @xs_id=(select  xs_id from inserted)
set @new_id=(select xs_id from deleted)
if(@xs_id!=@new_id)
 begin
 update cj set xs_id=@xs_id where xs_id=@new_id
 print '学生ID已经修改'
end
end
go
drop trigger t_update_xs
select * from xs where xs_id='001'
update xs set xs_id='001'  where xs_id='099'
select *from cj