SqlServer触发器的基本使用

--触发器分类:
--1,DML触发器
	--1.1 insert         新增的记录保存在inserted表中
	--1.2 update		 旧的记录在deleted中，新的在inserted中
	--1.3 delete         刚刚删除的记录保存在 deleted表中
	--1.4 instead of     用来指定用DML触发器中的操作代替触发语句的操作
--2,DDL触发器   
--练习常用的  insert update delete 触发器
go 
use db_Test;
if exists (select * from sysobjects where name='book')
drop table book;
create table book(
	bookId int primary key,
	bookName char(20),
	bookPrice float,
	publishTime date,
	bookNum int         --书本的剩余数量
);
--借书记录
if exists (select * from sysobjects where name='borrowedBook')
drop table borrowedBook;
create table borrowedBook(
	bookId int,
	borrwDate Date,
	bookName char(20),
	borrowNum int --借书数量
)
go
--创建存储过程,向book表中插入10条数据
if exists (select * from sysobjects where name='addBook')
drop procedure addBook;
go
create procedure addBook as
begin
	declare @i int;
	set @i=1;
	while(@i<10)
	begin
		--cast 将一个表达式转化成另外一个类型
		insert into book select @i,'book '+cast(@i as CHAR(1)),(10+@i)/10.0,GETDATE(),@i;
		set @i=@i+1;
	end
end

go
--创建触发器，当向借书记录中添加一条图书记录时，修改原来书本的剩余数量
if exists (select * from sysobjects where name='borrowBook_trigger')
drop trigger borrowBook_trigger;
go
create trigger borrowBook_trigger on borrowedBook after insert as
begin
	declare @bookId int;
	declare @borrowNum int;
	--从inserted表中得到插入的数据的信息
	select @bookId=bookId,@borrowNum=borrowNum from inserted;
	--修改book表中的信息
	update book set bookNum=bookNum-@borrowNum where bookId=@bookId;
end

--创建触发器，当向借书记录中删除一条图书记录时，修改原来书本的剩余数量
if exists (select * from sysobjects where name='returnBook_trigger')
drop trigger returnBook_trigger;
go
create trigger returnBook_trigger on borrowedBook after delete as
begin
	declare @bookId int;
	declare @borrowNum int;
	--从deleted表中得到插入的数据的信息
	select @bookId=bookId,@borrowNum=borrowNum from deleted;
	--修改book表中的信息
	update book set bookNum=bookNum+@borrowNum where bookId=@bookId;
end

--创建触发器，当修改图书的信息(书名)时候,对应修改借书表中的信息(书名)
if exists (select * from sysobjects where name='updateBook_trigger')
drop trigger updateBook_trigger;
go
create trigger updateBook_trigger on book after update as
begin
	declare @bookId int;
	declare @newName char(20);
	--从inserted表中得到修改的数据的信息
	select @bookId=bookId,@newName=bookName from inserted;
	--修改borrowedBook表中的信息
	update borrowedBook set bookName=@newName where bookId=@bookId;
end
go
-- 测试数据:


--执行存储过程,向book表中插入数据
exec addBook;
--查看图书记录
select * from book;
--查看借书记录
select * from borrowedBook;

--向借书表中插入一条记录 借走id为2的书一本
insert into borrowedBook values (2,GETDATE(),'book2',1);
select * from book;
select * from borrowedBook;

--修改书本id为2的书名
update book set bookName='newBook' where bookId=2;
select * from book;
select * from borrowedBook;

--从借书表中删除记录
delete from borrowedBook where bookId=2;
select * from book;
select * from borrowedBook;