转:http://www.cnblogs.com/chengxingliang/archive/2013/09/11/3307458.html
触发器
触发器的基础知识
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之前定义临时表 |
用法:无表关联操作,只作为中间集进行数据处理,建议用表变量;有表关联,且不能确定数据量大小的情况下,建议用临时表。