sql7.sql

微软2005 sql是类程序原代码

--------------------------------------
--									--
--				作业					--
--									--
--------------------------------------

--1	sql2005的触发器是如何分类的？
	--答：sql2005的触发器按大类分为：DML触发器和DLL触发器。而DML触发器按照功能的不同分为AFTER触发器 和INSTEAD OF触发器;其中AFTER触发器
	--又分为以下三种类型的触发器:	INSERT 触发器,UPDATE 触发器,DELETE 触发器.

--2	after触发器和instead of触发器，哪个在检查列的约束前触发，哪个后触发？
	--答：在检查列的约束后触发;instead of触发器在前触发.其中，instead of触发器在触发前会先检查有关语句是否符合检查列的约束,
		--而after触发器则不会。

--3	创建一个员工表和一个部门表，部门表的最后一个字段是部门人数，插入若干记录，
--要求在员工表插入或删除新记录的同时，必须更新该员工所在部门的人数
--提示：分别建立一个insert触发器和一个delete触发器，员工表的部门列引用部门表的编号
use master 
go
if exists (select *from sysdatabases where name='Work')
	drop database Work
go
create database Work
go
use Work 
go
go
create table Section
(
	Scid int identity(1001,1) primary key,
	ScName varchar(20), 
	ScTatol int 
)
go
create table Employee 
(
	EyId int primary key,
	EyName varchar(10), 
	EyAge varchar(3),
	Scid int references Section
)
go

insert into Section values('财务部',3)
insert into Section values('生产部',3)
insert into Section values('设计部',2)
go
insert into Employee values(200278,'邓斌',25,1001)
insert into Employee values(200279,'陶伟',35,1002)
insert into Employee values(200232,'周锦堂',45,1003)
insert into Employee values(200234,'金红',26,1002)
insert into Employee values(200256,'李丽',21,1001)
insert into Employee values(200252,'陶兰',54,1002)
insert into Employee values(200272,'王超',19,1003)
insert into Employee values(200270,'汪小芳',22,1001)

go
--------------------------------------------------------------
-------------     建立insert 触发器    ----------------------
--------------------------------------------------------------
if exists (select *from Sysobjects where name='trg_insertWork')
	drop trigger trg_insertWork
go
create trigger trg_insertWork
on Employee
for insert 
as
	declare @id int
	set @id=(select Scid from inserted)
	update  Section set ScTatol=ScTatol+1 where Scid=@id
go

insert into Employee values(200254,'方勇',28,1003)
select *from Section
select *from Employee

--------------------------------------------------------------
-------------      建立delete  触发器    --------------------
--------------------------------------------------------------
if exists (select *from sysobjects where name='trg_deletetWork')
	drop trigger trg_deleteWork
go
create trigger trg_deleteWork
on Employee 
for delete 
as
	declare @id int 
	set @id =(select Scid from deleted)
	update Section set ScTatol=ScTatol-1 where Scid=@id
go
delete from Employee where Eyname='方勇'
select *from Employee 
select *from Section 


--4	testpaper表存放的是所有试卷的信息，现在需要实现如下功能：
--试卷需要有足够的数量，以保证考试的进行，
--如果有考试，相应试卷的数量就应该减少
--如语句：update testpaper set amount = amount - 40 where id = 102
--如果教务的老师复印了试卷，那么该试卷的数量就应该增加
--如语句：update testpaper set amount = amount + 30 where id = 124
--现要求编写一个针对更新操作的触发器，无论更新语句是+还是-
--如果更新后试卷的数量小于0，则不允许更新，提示‘试卷不够，不能考试’
--如果更新后试卷的数量小于50，则提示‘试卷快用完了，请加印’
--如果更新后试卷的数量大于300，则提示‘试卷非常充足’
if exists (select *from sysobjects where name='testpaper')
	drop table testpaper
go
use Work
go
create table testpaper
(
	id int  identity(101,1) primary key,
	testType varchar(10),
	testNumber int 
)
go
insert into testpaper values('语文',350)
insert into testpaper values('英语',120)
insert into testpaper values('数学',32)
insert into testpaper values('理综',65)
go
if exists (select *from sysobjects where name ='trg_update')
	drop trigger trg_update
go
create trigger trg_update
on testpaper
for update 
as
	declare @number int
	--declare @Amount int 
	--declare @id int
	set @number =(select testNumber from inserted)----更新后的语句
	--set @Amount =(select testNumber from deleted)----更新前的语句
	if @number <0
	begin
		raiserror('试卷不够，不能考试.更新失败！！',3,101)
		rollback transaction 
	end
	else if @number <50
	begin
		print '试卷快用完了，请尽快复印！！！'
	end
	else if @number >300
	begin
		print '试卷非常充足'
	end
go

update testpaper set testNumber =testNumber-10 where id=104
select *from testpaper


--5	student表的clsId列引用自class表的id列，根据外键约束，新插入学生
--信息的班级编号必须在class表中已经存在，现要求编写一个触发器，判断新学生
--所在班级是否在class存在，如果存在，则插入学生信息，并提示‘已添加学生信息1条’
--如果不存在，则先在class表中插入该学生所在班级的记录，然后再插入学生信息到
--student表中，并提示‘已添加学生信息1条，及班级信息1条’
--提示：想想使用after触发器能否完成此功能，如果不能应当使用什么触发器？
--该触发器是针对哪个表的？是student表还是class表？

if exists (select *from sysobjects where name='StuInfo')
	drop table StuInfo
go
if exists (select *From sysobjects where name='Class')
	drop table Class
go
use Work
go
create table StuInfo
(
	Stuid int identity(1,1) primary key,
	StuName varchar(10),
	StuAge int ,
	StuSex varchar(2),
	clsid varchar(10) 
)
go
create table Class 
(
	id int identity(101,1) ,
	clsid varchar(10) primary key,
	Amount int 
)
go
alter table StuInfo add constraint  FK_StuInfo_clsid foreign key(clsid) references Class(clsid)
go
insert into Class values('0601',50)
insert into Class values('0602',45)
insert into Class values('0603',48)
insert into Class values('0604',27)
insert into Class values('0605',60)

go
insert into stuInfo values('曹阳',25,'男','0601')
insert into stuInfo values('陶伟',17,'男','0604')
insert into stuInfo values('汪小芳',20,'女','0603')
insert into stuInfo values('将小天',21,'男','0603')
insert into stuInfo values('李小丽',19,'女','0602')
insert into stuInfo values('周锦堂',23,'男','0601')
insert into stuInfo values('堂英',20,'女','0602')
insert into stuInfo values('刘德华',25,'男','0604')
insert into stuInfo values('刘翔',24,'男','0604')
insert into stuInfo values('陶岚',25,'女','0603')
insert into stuInfo values('小李',18,'男','0601')
insert into stuInfo values('张翠山',19,'男','0602')

----------------------------------------------------------------------------------------------------
------------------------------------      使用after触发器   ----------------------------------------
----------------------------------------------------------------------------------------------------
if exists (select *from sysobjects where name ='trg_insert')
	drop trigger trg_insert
go
create trigger trg_insert 
on StuInfo
for insert
as
	declare @Clsid	varchar(10)
	declare @erro int 
	set @erro=0
	set @Clsid =(select Clsid from inserted)
	if (@Clsid not in(select Clsid from Class))
	begin
		--update Class set Clsid=@Clsid,Amount=0 where(Clsid not in (select Clsid from Class))---不能这样写，为什么？？
		insert into Class values(@Clsid,0)
		set @erro=@erro+@@error
		update Class set Amount=Amount+1 where Clsid=@Clsid
		set @erro=@erro+@@error
		if @erro<>0
		begin
			raiserror('插入失败，操作中断！！',3,110)
			rollback transaction 
		end
		else
			print '已添加学生信息1条及班级信息1条'
	end
	else
	begin
		update Class set Amount=Amount+1 where Clsid=@Clsid
		print '已添加学生信息1条及班级信息1条'
	end
go
select *from StuInfo
select *from Class
insert into stuInfo values('邓来',20,'男','0609') 
delete from Class where Clsid=0606

----------------------------------------------------------------------------------------------------
------------------------------------      使用instead of 触发器   ----------------------------------
----------------------------------------------------------------------------------------------------
select *from StuInfo
select *from Class 
 if exists(select *from sysobjects where name='trg_insteadof')]
	drop trigger trg_insteadof
go
create trigger trg_insteadof
on StuINfo
instead of
as
	 
go