触发器
触发器的基础知识
create trigger tr_name
on table/view
{for | after | instead of } [update][,][insert][,][delete]
[with encryption]
as {batch | if update (col_name) [{and|or} update (col_name)] }
说明：
1 tr_name ：触发器名称
2 on table/view ：触发器所作用的表。一个触发器只能作用于一个表
3 for 和after ：同义
4 after 与instead of :sql 2000新增项目afrer 与 instead of 的区别
After
在触发事件发生以后才被激活,只可以建立在表上
Instead of
代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上
5 insert、update、delete：激活触发器的三种操作，可以同时执行，也可选其一
6 if update (col_name)：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete 操作只对行有影响，
所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。
7 触发器执行时用到的两个特殊表：deleted ,inserted
deleted 和inserted 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一样的，只是存放 的数据有差异。
8 说明deleted 与inserted 数据的差异
deleted 与inserted 数据的差异
Inserted 存放进行insert和update 操作后的数据
Deleted 存放进行delete 和update操作前的数据
注意：update 操作相当于先进行delete 再进行insert ,所以在进行update操作时，修改前的数据拷贝一条到deleted 表中，修改后的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中
触发器典型示例
if exists(select name from sysobjects where xtype='tr' and name='tri_updateStudent')
begin
drop trigger tri_UpdateStudent
end
go
create trigger tri_UpdateStudent
on dbo.student
for update
as
if update(Sage)
begin
update student set sage=s.sage+d.sage from student s,deleted d where s.studentid=d.studentid
end
go
存储过程
存储过程的优点
A、 存储过程允许标准组件式编程
B、 存储过程能够实现较快的执行速度
C、 存储过程减轻网络流量
D、 存储过程可被作为一种安全机制来充分利用
存储过程的实例
if exists(select * from sysobjects where xtype='p' and name='proc_Student')
begin
drop proc proc_student
end
go
create proc proc_Student
@name varchar(255),
@age varchar(255)
as
begin tran
select * from student where sname=@name and sage=@age
if @@ERROR<>0
begin
rollback tran
insert into student(studentid,sname,sage) values (1,@name,@age)
return 0
end
else
begin
commit tran
select * from student
end
go
exec proc_student '程兴亮',1;
表变量
表变量定义：
表变量创建的语法类似于临时表，区别就在于创建的时候，必须要为之命名。表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量，像我们常用到的，如@@Error代表错误的号，@@RowCount代表影响的行数。
DECLARE @News table ( News_id int NOT NULL, NewsTitle varchar(100), NewsContent varchar(2000), NewsDateTime datetime ) INSERT INTO @News (News_id, NewsTitle, NewsContent, NewsDateTime) VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM @News
临时表
临时表定义：
临时表与永久表相似，只是它的创建是在Tempdb中，它只有在一个数据库连接结束后或者由SQL命令DROP掉，才会消失，否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志，虽它们在Tempdb中体现，是分配在内存中的，它们也支持物理的磁盘，但用户在指定的磁盘里看不到文件。
临时表分为本地和全局两种，本地临时表的名称都是以“#”为前缀，只有在本地当前的用户连接中才是可见的，当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀，创建后对任何用户都是可见的，当所有引用该表的用户断开连接时被删除
CREATE TABLE dbo.#News
(
News_id int NOT NULL,
NewsTitle varchar(100),
NewsContent varchar(2000),
NewsDateTime datetime
)
INSERT INTO dbo.#News (News_id, NewsTitle, NewsContent, NewsDateTime)
VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())
SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM dbo.#News
DROP TABLE dbo.[#News]
表变量和临时表对比总结
|
特性 |
表变量 |
临时表 |
|
作用域 |
当前批处理 |
当前会话，嵌套存储过程，全局：所有会话 |
|
使用场景 |
自定义函数，存储过程，批处理 |
自定义函数，存储过程，批处理 |
|
创建方式 |
DECLARE statement only.只能通过DECLEARE语句创建 |
CREATE TABLE 语句 SELECT INTO 语句. |
|
表名长度 |
最多128字节 |
最多116字节 |
|
列类型 |
可以使用自定义数据类型 可以使用XML集合 |
自定义数据类型和XML集合必须在TempDb内定义 |
|
Collation |
字符串排序规则继承自当前数据库 |
字符串排序规则继承自TempDb数据库 |
|
索引 |
索引必须在表定义时建立 |
索引可以在表创建后建立 |
|
约束 |
PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用，但必须在表建立时声明 |
PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用，可以在任何时后添加，但不能有外键约束 |
|
表建立后使用DDL (索引，列) |
不允许 |
允许. |
|
数据插入方式 |
INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC). |
INSERT 语句, 包括 INSERT/EXEC. SELECT INTO 语句. |
|
Insert explicit values into identity columns (SET IDENTITY_INSERT). |
不支持SET IDENTITY_INSERT语句 |
支持SET IDENTITY_INSERT语句 |
|
Truncate table |
不允许 |
允许 |
|
析构方式 |
批处理结束后自动析构 |
显式调用 DROP TABLE 语句. |
|
事务 |
只会在更新表的时候有事务，持续时间比临时表短 |
正常的事务长度，比表变量长 |
|
存储过程重编译 |
否 |
会导致重编译 |
|
回滚 |
不会被回滚影响 |
会被回滚影响 |
|
统计数据 |
不创建统计数据，所以所有的估计行数都为1,所以生成执行计划会不精准 |
创建统计数据，通过实际的行数生成执行计划。 |
|
作为参数传入存储过程 |
仅仅在SQL Server2008, 并且必须预定义 user-defined table type. |
不允许 |
|
显式命名对象 (索引, 约束). |
不允许 |
允许，但是要注意多用户的问题 |
|
动态SQL |
必须在动态SQL中定义表变量 |
可以在调用动态SQL之前定义临时表 |
用法：无表关联操作，只作为中间集进行数据处理，建议用表变量；有表关联，且不能确定数据量大小的情况下，建议用临时表。