第八天 -- 《2014-07-15 三层架构》1 -- 触发器、索引、临时表

一、上午《01、触发器》 -- 触发器

1、创建触发器

触发器相当于一个特殊的存储过程，被DBMS调用。

触发器的事件只能是表的insert/ delete / update操作。

SqlServer触发器的触发方式有两种：（1）在表（增删改）操作后（after）触发，（2）在操作前触发，此时表操作被替换为执行触发器中定义的sql代码。

--语法：
--create  trigger  tr_触发器名称
--on  表名   after（或者for）/  instead of   insert/ delete / update
--as
-- 任意的逻辑代码或 存储过程
--go

--创建触发器例子一：
if exists(select * from sysobjects where name='tr_grade_insert')
drop trigger tr_grade_insert
go

create  trigger  tr_grade_insert
on  grade  for  insert --为grade表创建触发器，在你对grade表进行插入操作后触发，for和after等效
as
 select * from student  --可以有若干sql语句、事务、存储过程调用
go

 

2、inserted、deleted临时表

在delete数据的时候，可以假定数据库将要删除的数据放到一个deleted临时表中，我们可以向读取普通的表一样，select  字段  from deleted 。

而insert的时候道理一样，只不过是把要插入的数据放在inserted表中。

更新操作可以认为是执行了两个操作，先把那一行记录delete掉，然后再insert，所以deleted就是更新前的数据，inserted是更新后的数据。所以就不再有updated表了。

（1）以inserted为例说明这两个临时表

--在插入的时候，输出插入记录（作为结果集）
insert into grade output inserted.id, inserted.classname  values('亲爱sdas的')   --结果集为插入的一条记录


--删除记录的时候，输出删除了哪些（作为结果集）
delete from OdsPC output deleted.*  where id > 7   --结果集为删掉的那几条记录

 

 

（2）在 触发器中使用inserted和deleted临时表

--插入后触发器查询两个临时表
if exists(select * from sysobjects where name='tr_grade_insert')
drop trigger tr_grade_insert
go
create trigger tr_grade_insert
on grade for insert --为grade表创建触发器，在你对grade表进行插入操作后触发
as
print 'inserted表存储操作之后的 与当前操作相关的数据，而与之前表的数据无关'
select * from inserted 
print 'deleted表存储操作之前的数据'
select * from deleted
go

insert into grade values('亲爱sdas的')

if exists(select * from sysobjects where name='tr_grade_delete')
drop trigger tr_grade_delete
go
create trigger tr_grade_delete
on grade after delete --为grade表创建触 发器，在你对grade表进行插入操作后触 发
as
print 'inserted表存储操作之后的 与当前操作相关的数据，而与之前表的数据无关'
select * from inserted 
print 'deleted表存储操作之前的数据'
select * from deleted
go

delete from grade where ClassId>18

if exists(select * from sysobjects where name='tr_grade_update')
drop trigger tr_grade_update
go
create trigger tr_grade_update
on grade after update --为grade表创建触 发器，在你对grade表进行插入操作后触 发
as
print 'inserted表存储操作之后的 与当前操作相关的数据，而与之前表的数据无关'
select * from inserted 
print 'deleted表存储操作之前的数据'
select * from deleted
go

update grade set classname='aaaaa' where ClassId>16

 

 

 

3、注意

（1）SQLServer对某表的某个操作（如insert）允许定义多个触发器（名字不一样）。

当该操作执行时，DBMS会分别调用它们。最后产生的结果可能不是你所期望的那样。所以一个表的一个操作，最好只定义一个触发器。

 

 

二、上午《02、索引》-- 提高检索速度

1、索引的概念

（1）对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。
（2）如果没有目录而且汉字无序排版，查汉语字典就要一页一页翻，而有了目录（汉字按拼音排序）只要查询目录即可。为了提高检索的速度，可以为经常进行查询的列添加索引，相当于创建目录。（实际上是每个索引）

（3）索引意味着排序，排序后则可更高效的查询。
（4）创建索引的方式，在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。
（5）使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的列上(经常用在Where子句中的列)创建索引。
（6）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。

（7）创建索引： create index  IX_Student_studentname  on  Student(studentname)            --on 表（列）

　　  删除索引：  drop index T8.IX_T8_tage
　　

if  exists(select  *  from sysindexes where name = 'IX_Student_studentname')
    drop  index  Student.IX_Student_studentname
go

create  clustered  index IX_Student_studentname   --创建一个聚集的索引。非聚集索引用 nonclustered
on  Student(studentname)                          -- on 表（列）

 

（8）索引的命名：IX_Employee_LoginID（即 IX_表名_列名）

 

2、按照索引排序（改动了原始表的排序）

（1）主键就是一种索引

（2）创建索引时，“创建为聚集的”设置为true后，表中的数据内容就会按照该索引列排序。（一张表中只能有一个索引或主键是聚集的）

 表中没有聚集索引的，默认按照主键排序

 

三、《03、自定义临时表》

自定义临时表创建后存放在系统数据库tempdb临时表之下

1、使用场景举例

如何将原始表的自增长主键值重新开始（从1再排一遍）？

（1）先用原始表student数据select into 临时表#newtemp。

（2）truncate 原始表student。

（3）再将临时表数据插入原始表。insert into student  select * from  #newtemp

--使自增长主键值重新开始排
select *  into  #newtemp  from  grade
truncate table grade
insert into  grade select classname from #newtemp

 

 

2、局部临时表（只针对当前对话使用）

表名加前缀#  如： #StuTemp

--局部临时表:只能在当前创建临时表的会话中使用。如果关闭了这么会话，那么临时表就自动消失
create table #temp
(
 --字段名称  类型  特征
cid int ,
cname nvarchar(50) 
)

 

3、全局临时表（只要创建全局临时表的会话没结束，所有会话都可以访问这个全局临时表）

表名加前缀##  如：##Temp

--全局临时表只要不关闭当前会话，那么在其它会话中还是可以使用，但是如果关闭当前会话，那么临时也会消失
create table ##temp
(
 --字段名称  类型  特征
cid int ,
cname nvarchar(50) 
)
insert into ##temp select * from grade
truncate table grade
insert into grade select cname from ##temp

 

4、临时表总结