--------------------------------------
--									--
--				作業					--
--									--
--------------------------------------

--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('試卷不夠，不能考試.更新失?。。?#039;,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('插入失敗，操作中斷?。?#039;,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