SQL 触发器实例讲解 1
SQL 触发器实例 1 定义： 何为触发器？在 SQL Server 里面也就是对某一个表的一定的操作，触发某种条件，从 而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于 Insert , Update , Delete 事件。 我为什么要使用触发器？比如，这么两个表： Create Table Student( StudentID int primary key, .... ) Create Table BorrowRecord( BorrowRecord StudentID BorrowDate ReturnDAte ... ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改 借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 等等。 这时候可以用到触发器。对于 1，创建一个 Update 触发器： Create Trigger truStudent On Student for Update As if Update(StudentID) begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted 时表 Where br.StudentID=d.StudentID d ,Inserted i --Deleted 和 Inserted 临 --在 Student 表中创建触发器 --为什么事件触发 --事件触发后所要做的事情 int identity(1,1), int , datetime, Datetime, --学生借书记录表 --流水号 --学号 --借出时间 --归还时间 --学生表 --学号
end 理解触发器里面的两个临时的表：Deleted , Inserted 。注意 Deleted 与 Inserted 分别 表示触发事件的表“旧的一条记录”和“新的一条记录”。 一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是： 虚拟表 Inserted 在表记录新增时 修改时 删除时 存放新增的记录 存放用来更新的新记录 不存储记录 虚拟表 Deleted 不存储记录 存放更新前的记录 存放被删除的记录
一个 Update 的过程可以看作为： 生成新的记录到 Inserted 表， 复制旧的记录到 Deleted 表，然后删除 Student 记录并写入新纪录。 对于 2，创建一个 Delete 触发器 Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 从这两个例子我们可以看到了触发器的关键：A.2 个临时的表；B.触发机制。 SQL 触发器实例 2 /* 建立虚拟测试环境，包含：表[卷烟库存表]，表[卷烟销售表]。 请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。 为了能更清晰的表述触发器的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。 */ USE Master GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’ 卷烟库存表’) DROP TABLE 卷烟库存表 GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’ 卷烟销售表’) DROP TABLE 卷烟销售表 GO
--业务规则：销售金额 = 销售数量 * 销售单价 业务规则。 CREATE TABLE 卷烟销售表 ( 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 购货商 VARCHAR(40) NULL, 销售数量 INT NULL, 销售单价 MONEY NULL, 销售金额 MONEY NULL ) GO --业务规则：库存金额 = 库存数量 * 库存单价 业务规则。 CREATE TABLE 卷烟库存表 ( 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 库存数量 INT NULL, 库存单价 MONEY NULL, 库存金额 MONEY NULL ) GO --创建触发器，示例 1 /* 创建触发器[T_INSERT_卷烟库存表]，这个触发器较简单。 说明： 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。 触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。 注意： [INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。 重要： 这两个系统表的结构同插入数据的表的结构。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷烟库存表’) DROP TRIGGER T_INSERT_卷烟库存表 GO CREATE TRIGGER T_INSERT_卷烟库存表 ON 卷烟库存表 FOR INSERT AS --提交事务处理 BEGIN TRANSACTION --强制执行下列语句，保证业务规则
UPDATE 卷烟库存表 SET 库存金额 = 库存数量 * 库存单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED) COMMIT TRANSACTION GO /* 针对[卷烟库存表]，插入测试数据： 注意，第一条数据（红塔山新势力）中的数据符合业务规则， 第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则， 第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。 第四条数据库存数量为 0。 请注意在插入数据后，检查[卷烟库存表]中的数据是否 库存金额 = 库存数量 * 库存单价。 */ INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额) SELECT ’红塔山新势力’,100,12,1200 UNION ALL SELECT ’红塔山人为峰’,100,22,NULL UNION ALL SELECT ’云南映像’,100,60,500 UNION ALL SELECT ’玉溪’,0,30,0 GO --查询数据 SELECT * FROM 卷烟库存表 GO /* 结果集 RecordId 卷烟品牌 库存数量 库存单价 库存金额 -------- ------------ -------- ------- --------1 红塔山新势力 100 12.0000 1200.0000 2 红塔山人为峰 100 22.0000 2200.0000 3 云南映像 100 60.0000 6000.0000 4 玉溪 0 30.0000 .0000 （所影响的行数为 4 行） */ --触发器示例 2 /*
创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。 说明: 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。 触发器功能： 实现业务规则。 业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。 否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷烟销售表’) DROP TRIGGER T_INSERT_卷烟销售表 GO CREATE TRIGGER T_INSERT_卷烟销售表 ON 卷烟销售表 FOR INSERT AS BEGIN TRANSACTION --检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零 IF NOT EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) ) BEGIN --返回错误提示 RAISERROR(’错误！该卷烟不存在库存，不能销售。’,16,1) --回滚事务 ROLLBACK RETURN END IF EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 库存数量 <= 0 ) BEGIN --返回错误提示 RAISERROR(’错误！该卷烟库存小于等于 0，不能销售。’,16,1) --回滚事务 ROLLBACK RETURN END
--对合法的数据进行处理 --强制执行下列语句，保证业务规则 UPDATE 卷烟销售表 SET 销售金额 = 销售数量 * 销售单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) DECLARE @卷烟品牌 VARCHAR(40) SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED) DECLARE @销售数量 MONEY SET @销售数量 = (SELECT 销售数量 FROM INSERTED) UPDATE 卷烟库存表 SET 库存数量 = 库存数量 - @销售数量, 库存金额 = (库存数量 - @销售数量)*库存单价 WHERE 卷烟品牌 = @卷烟品牌 COMMIT TRANSACTION GO --请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。 --针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT ’红塔山新势力’,’某购货商’,10,12,1200 GO --针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。 --触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT ’红塔山人为峰’,’某购货商’,10,22,2000 GO --针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。 --触发器将报错。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT ’红河 V8’,’某购货商’,10,60,600 GO /* 结果集 服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 15
错误！该卷烟不存在库存，不能销售。 */ --针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为 0。 --触发器将报错。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT ’玉溪’,’某购货商’,10,30,300 GO /* 结果集 服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 29 错误！该卷烟库存小于等于 0，不能销售。 */ --查询数据 SELECT * FROM 卷烟库存表 SELECT * FROM 卷烟销售表 GO /* 补充： 1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活 处理； 2、关于触发器要理解并运用好 INSERTED ，DELETED 两个系统表； 3、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考： Trigger 语法 CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] --用于加密触发器 { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] }]
sql_statement [ ...n ] } } 4、关于触发器，还应该注意 (1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。 (2)、触发器中不允许以下 Transact-SQL 语句： ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG (3)、触发器最多可以嵌套 32 层。 */ --修改触发器 --实质上，是将 CREATE TRIGGER ... 修改为 ALTER TRIGGER ...即可。 --删除触发器 DROP TRIGGER xxx GO --删除测试环境 DROP TABLE 卷烟库存表 GO DROP TABLE 卷烟销售表 GO DROP TRIGGER T_INSERT_卷烟库存表 GO DROP TRIGGER T_INSERT_卷烟销售表 GO ################################################## ################ 触发器的基础知识和例子 ：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 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的， 其结构与触发器作用的表结构是一 样的，只是存放 的数据有差异。 续 下面表格说明 deleted 与 inserted 数据的差异 deleted 与 inserted 数据的差异 Inserted 存放进行 insert 和 update 操作后的数据 Deleted 存放进行 delete 和 update 操作前的数据 注意：update 操作相当于先进行 delete 再进行 insert ,所以在进行 update 操作时，修改前 的数据拷贝一条到 deleted 表中，修改后 的数据在存到触发器作用的表的同时，也同时生成一条拷贝到 insered 表中 接(一) --触发器示例 2 /* 创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。 说明: 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。 触发器功能： 实现业务规则。 业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。 否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷烟销售表’) DROP TRIGGER T_INSERT_卷烟销售表 GO CREATE TRIGGER T_INSERT_卷烟销售表 ON 卷烟销售表 FOR INSERT
AS BEGIN TRANSACTION --检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零 IF NOT EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) ) BEGIN --返回错误提示 RAISERROR(’错误！该卷烟不存在库存，不能销售。’,16,1) --回滚事务 ROLLBACK RETURN END IF EXISTS ( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 库存数量 <= 0 ) BEGIN --返回错误提示 RAISERROR(’错误！该卷烟库存小于等于 0，不能销售。’,16,1) --回滚事务 ROLLBACK RETURN END --对合法的数据进行处理 --强制执行下列语句，保证业务规则 UPDATE 卷烟销售表 SET 销售金额 = 销售数量 * 销售单价 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) DECLARE @卷烟品牌 VARCHAR(40) SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED) DECLARE @销售数量 MONEY
SET @销售数量 = (SELECT 销售数量 FROM INSERTED) UPDATE 卷烟库存表 SET 库存数量 = 库存数量 - @销售数量, 库存金额 = (库存数量 - @销售数量)*库存单价 WHERE 卷烟品牌 = @卷烟品牌 COMMIT TRANSACTION GO --请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。 --针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT ’红塔山新势力’,’某购货商’,10,12,1200 GO --针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售 数量。 --触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT ’红塔山人为峰’,’某购货商’,10,22,2000 GO
SQL 拥有很多可用于计数和计算的内建函数。 拥有很多可用于计数和计算的内建函数。
函数的语法
内建 SQL 函数的语法是：
SELECT function(列) FROM 表
函数的类型
在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：
? ?
Aggregate 函数 Scalar 函数
合计函数（ 合计函数（Aggregate functions） ）
Aggregate 函数的操作面向一系列的值，并返回一个单一的值。
注释：如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，则这个 SELECT 必 注释： 须使用 GROUP BY 语句！
"Persons" table (在大部分的例子中使用过 在大部分的例子中使用过) 在大部分的例子中使用过 Name Age
Adams, John
38
Bush, George
33
Carter, Thomas
28
MS Access 中的合计函数 函数 描述 返回某列的平均值 返回某列的行数（不包括 NULL 值） 返回被选行数 返回在指定的域中第一个记录的值 返回在指定的域中最后一个记录的值 返回某列的最高值 返回某列的最低值
AVG(column)
COUNT(column)
COUNT(*)
FIRST(column)
LAST(column)
MAX(column)
MIN(column)
STDEV(column)
STDEVP(column) 返回某列的总和
SUM(column)
VAR(column)
VARP(column)
在 SQL Server 中的合计函数 函数 描述 返回某列的行数
AVG(column)
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG 返回某列的行数（不包括 NULL 值） 返回被选行数 返回相异结果的数目 返回在指定的域中第一个记录的值（SQLServer2000 不支持） 返回在指定的域中最后一个记录的值（SQLServer2000 不支持） 返回某列的最高值 返回某列的最低值
COUNT(column)
COUNT(*)
COUNT(DISTINCT column)
FIRST(column)
LAST(column)
MAX(column)
MIN(column)
STDEV(column)
STDEVP(column) 返回某列的总和
SUM(column)
VAR(column)
VARP(column)
SQLServer 游标简介与使用说明
游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了 在结果集中一次以行或者多行前进或向后浏览数据的能力。 我们可以把游标当作一个指针， 它可 以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。
1.游标的组成 游标的组成
游标包含两个部分：一个是游标结果集、一个是游标位置。 游标结果集：定义该游标得 SELECT 语句返回的行的集合。游标位置：指向这个结果集某 一行的当前指针。
2.游标的分类 游标的分类
游标共有 3 类：API 服务器游标、Transaction-SQL 游标和 API 客户端游标。 其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。 API 服务器游标 API 服务器游标主要应用在服务上，当客户端的应用程序调用 API 游标函数时，服务器会 对 API 函数进行处理。使用 API 函数和方法可以实现如下功能： （１）打开一个连接。 （２）设置定义游标特征的特性或属性，API 自动将游标影射到每个结果集。 （３）执行一个或多个 Transaction-SQL 语句。 （４）使用 API 函数或方法提取结果集中的行。 API 服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标（Primary key） 静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，（静态游标始终是只 静态游标 读的）。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不反映数据库中作的 任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行； 组成结果集的行被其他用户更新， 新的数据值不会显示在静态游标中； 但是静态游标会显示打开 游标以后从数据库中删除的行。
动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的 动态游标 行数据值、顺序和成员每次提取时都会改变。 只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标也反映对 只进游标 结果集所做的所有更改。 键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及 键集驱动游标 行的顺序是固定的， 键集在游标打开时也会存储到临时工作表中， 对非键集列的数据值的更改在 用户游标滚动的时候可以看见， 在游标打开以后对数据库中插入的行是不可见的， 除非关闭重新 打开游标。
Transaction-SQL 游标 该游标是基于 Declare Cursor 语法，主要用于 Transaction-SQL 脚本、存储过程以及 触发器中。Transaction-SQL 游标在服务器处理由客户端发送到服务器的 Transaction-SQL 语句。 在存储过程或触发器中使用 Transaction-SQL 游标的过程为： （１）声明 Transaction-SQL 变量包含游标返回的数据。为每个结果集列声明一个变量。 声明足够大的变量来保存列返回的值， 并声明变量的类型为可从数据类型隐式转换得到的数据类 型。 （２）使用 Declare Cursor 语句将 Transaction-SQL 游标与 Select 语句相关联。还可 以利用 Declare Cursor 定义游标的只读、只进等特性。 （３）使用 Open 语句执行 Select 语句填充游标。 （４）使用 Fetch Into 语句提取单个行，并将每列中得数据移至指定的变量中。注意：其 他 Transaction-SQL 语句可以引用那些变量来访问提取的数据值。Transaction-SQL 游标不 支持提取行块。 （５）使用 Close 语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使 用 Open 命令打开继续使用，只有调用 Deallocate 语句才会完全释放。 客户端游标 该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端 的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。
3.游标的生命周期 游标的生命周期
游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放 游标。 声明游标是为游标指定获取数据时所使用的 Select 语句，声明游标并不会检索任何数据， 声明游标 它只是为游标指明了相应的 Select 语句。 Declare 游标名称 Cursor 参数 声明游标的参数 （１）Local 与 Global:Local 表示游标的作用于仅仅限于其所在的存储过程、触发器 以及批处理中、执行完毕以后游标自动释放。Global 表示的是该游标作用域是整个会话层。由 连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。 （２）Forward_only 与 Scroll:前者表示为只进游标，后者表示为可以随意定位。默 认为前者。 （３）Static、Keyset 与 Dynamic: 第一个表示定义一个游标，其数据存放到一个 临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不 反映对基表所作的修改，并且该游标不允许修改。Keyset 表示的是，当游标打开时，键集驱动 游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic 表示的是滚动游标时，动态 游标反映对结果集内所有数据的更改。 （４）Read_only 、Scroll_Locks 与 Optimistic：第一个表示的是只读游标，第二 个表示的是在使用的游标结果集数据上放置锁， 当行读取到游标中然后对它们进行修改时， 数据 库将锁定这些行，以保证数据的一致性。Optimistic 的含义是游标将数据读取以后，如果这些 数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。 标准游标： Declare MyCursor Cursor For Select * From Master_Goods 只读游标 Declare MyCusror Cursor For Select * From Master_Goods For Read Only 可更新游标
Declare MyCusror Cursor For Select * From Master_Goods For UpDate 打开游标使用 Open 语句用于打开 Transaction-SQL 服务器游标，执行 Open 语句的过 打开游标 程中就是按照 Select 语句进行填充数据，打开游标以后游标位置在第一行。 打开游标 全局游标：Open Global MyCursor 局部游标: Open MyCursor
读取游标数据：在打开游标以后，使用 Fetch 语句从 Transaction-SQL 服务器游标中检 读取游标数据 索特定的一行。使用 Fetch 操作，可以使游标移动到下一个记录，并将游标返回的每个列得数 据分别赋值给声明的本地变量。 Fetch [Next | Prior | First | Last | Absolute n MyCursor Into @GoodsID,@GoodsName 其中:Next 表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。 默认的读取选项为 Next Prior 表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回， 并且把游标置于第一行之前。 First 表示返回结果集中的第一行，并且将其作为当前行。 Last 表示返回结果集中的最后一行，并且将其作为当前行。 Absolute n 如果 n 为正数，则返回从游标头开始的第 n 行，并且返回行变成 | Relative n ] From
新的当前行。如果 n 为负，则返回从游标末尾开始的第 n 行，并且返回行为新的当前行，如果 n 为 0，则返回当前行。 Relative n 如果 n 为正数，则返回从当前行开始的第 n 行，如果 n 为负,则
返回从当前行之前的第 n 行，如果为 0，则返回当前行。 关闭游标调用的是 Close 语句，方式如下：Close Global MyCursor 关闭游标 MyCursor 释放游标调用的是 Deallocate 语句，方法如下：Deallocate Glboal 释放游标 MyCursor Deallocate MyCursor Close
游标实例: 游标实例 Declare MyCusror Cursor Scroll For Select * From Master_Goods Order By GoodsID Open MyCursor Fetch next From MyCursor Into @GoodsCode,@GoodsName While(@@Fetch_Status = 0) Begin Begin Select @GoodsCode = Convert(Char(20),@GoodsCode) Select @GoodsName = Convert(Char(20),@GoodsName) PRINT @GoodsCode + ':' + @GoodsName End Fetch next From MyCursor Into @GoodsCode,@GoodsName End Close MyCursor Deallocate MyCursor
修改当前游标的数据方法如下: UpDate Master_Goods Set GoodsName = 'yangyang8848' Where Current Of MyCursor; 删除当前游标行数据的方法如下: Delete From Master_Goods Where Current Of MyCursor
Select @@CURSOR_ROWS 可以得到当前游标中存在的数据行数。注意：此变量为一 个连接上的全局变量，因此只对应最后一次打开的游标。
SQLServer 存储过程简介与使用方法
Sql Server 的存储过程是一个被命名的存储在服务器上的 Transacation-Sql 语句集合, 是封装重复性工作的一种方法,它支持用户声明的变量、条件执行和其他强大的编程功能。 存储过程相对于其他的数据库访问方法有以下的优点： （1）重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。 （2）提高性能。存储过程在创建的时候就进行了编译，将来使用的时候不用再重新编 译。一般的 SQL 语句每执行一次就需要编译一次，所以使用存储过程提高了效率。 （3）减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称 以及参数就可以了，因此降低了网络传输的数据量。 （4）安全性。参数化的存储过程可以防止 SQL 注入式的攻击，而且可以将 Grant、 Deny 以及 Revoke 权限应用于存储过程。 存储过程一共分为了三类：用户定义的存储过程、扩展存储过程以及系统存储过程。 其中，用户定义的存储过程又分为 Transaction-SQL 和 CLR 两种类型。 Transaction-SQL 存储过程是指保存的 Transaction-SQL 语句集合，可以接受和返回用 户提供的参数。 CLR 存储过程是指对.Net Framework 公共语言运行时(CLR)方法的引用，可以接受和返 回用户提供的参数。他们在.Net Framework 程序集中是作为类的公共静态方法实现的。（本 （ 文就不作介绍了） 文就不作介绍了） 创建存储过程的语句如下： Code
[schema_name]: 代表的是存储过程所属的架构的名称 例如：
Create Schema yangyang8848 Go Create Proc yangyang8848.AllGoods As Select * From Master_Goods Go 执行：Exec AllGoods 发生错误。 执行：Exec yangyang8848.AllGoods 正确执行。 [；Number]: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语 句可将这些分组过程一起删除。 例如： Create Proc S1 ;1 AS Select * From Master_Goods Go Create Proc S1 ;2 As Select * From Master_Location Go 创建完毕了两个存储过程。它们在同一个组 S1 里，如果执行 Exec S1 则存储过程默 认执行 Exec S1 ;1 。如果我们想得到所有据点信息则需要执行 Exec S1 ;2。当我们要删除 存储过程的时候，只能执行 Drop Exec S1 则该组内所有的存储过程被删除。 [@ parameter]: 存储过程中的参数，除非将参数定义的时候有默认值或者将参数设置为 等于另一个参数，否则用户必须在调用存储过程的时候为参数赋值。 存储过程最多有 2100 个参数。 例如： Create Proc yangyang8848.OneGoods @GoodsCode varchar(10) As Select * From Master_Goods Where GoodsCode = @GoodsCode Go
触发器 一﹕触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。 二﹕SQL Server为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后﹐与该触发器相关的这两个表也被删除。Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。 三﹕Instead of 和After触发器 SQL Server提供了两种触发器﹕Instead of 和After 触发器。 这两种触发器的差别在于他们被激活的操作﹕ Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外﹐Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。 After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After 触发器被激活之前发生。After触发器只能用于表。一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。 INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。通常不能在一个基于连接的视图上进行DELETE操作，可以编写一个INSTEAD OF DELETE触发器来实现删除。可以访问那些如果视图是一个真正的表时已经被删除的数据行，它将把删除的行存储在deleted临时表中。 本次练习将通过具体的例子介绍如何使用INSTEAD OF触发器，使读者掌握INSTEAD OF 触发器的使用方法。 向student表中插入数据时，检查学号是否存在于student表中，如存在则进行插入操作,否则就不插入，具体语句如下所示： CREATE TRIGGER [checkid] ON dbo.student INSTEAD OF insert AS IF NOT EXISTS(SELECT * FROM dbo.student WHERE ID=(SELECT ID FROM INSERTED)) BEGIN ROLLBACK TRANSACTION PRINT '要处理记录的学号不存在！' END ELSE BEGIN INSERT NTO dbo.student select * from inserted
SQL触发器使用教程和命名规范 1，触发器简介 触发器（Trigger）是数据库对象的一种，编码方式类似存储过程，与某张表（Table）相关联，当有DML语句对表进行操作时，可以引起触发器的执行，达到对插入记录一致性，正确性和规范性控制的目的。在当年C/S时代盛行的时候，由于客户端直接连接数据库，能保证数据库一致性的只有数据库本身，此时主键（Primary Key），外键（Foreign Key），约束（Constraint）和触发器成为必要的控制机制。而触发器的实现比较灵活，可编程性强，自然成为了最流行的控制机制。到了B/S时代，发展成4层架构，客户端不再能直接访问数据库，只有中间件才可以访问数据库。要控制数据库的一致性，既可以在中间件里控制，也可以在数据库端控制。很多的青睐Java的开发者，随之将数据库当成一个黑盒，把大多数的数据控制工作放在了Servlet中执行。这样做，不需要了解太多的数据库知识，也减少了数据库编程的复杂性，但同时增加了Servlet编程的工作量。从架构设计来看，中间件的功能是检查业务正确性和执行业务逻辑，如果把数据的一致性检查放到中间件去做，需要在所有涉及到数据写入的地方进行数据一致性检查。由于数据库访问相对于中间件来说是远程调用，要编写统一的数据一致性检查代码并非易事，一般采用在多个地方的增加类似的检查步骤。一旦一致性检查过程发生调整，势必导致多个地方的修改，不仅增加工作量，而且无法保证每个检查步骤的正确性。触发器的应用，应该放在关键的，多方发起的，高频访问的数据表上，过多使用触发器，会增加数据库负担，降低数据库性能。而放弃使用触发器，则会导致系统架构设计上的问题，影响系统的稳定性。 2，触发器示例 触发器代码类似存储过程，以PL/SQL脚本编写。下面是一个触发器的示例： 新建员工工资表salary create table SALARY ( EMPLOYEE_ID NUMBER, --员工ID MONTH VARCHAR2(6), --工资月份 AMOUNT NUMBER --工资金额 ) 创建与salary关联的触发器salary_trg_rai 1 Create or replace trigger salary_trg_rai 2 After insert on salary 3 For each row
数据库原理与应用 课程设计A报告 姓名：袁一帆学号：20121480 学院(系)：管理学院专业：信息管理与信息系统
班级：12级信管1 班
襄阳迈博信息科技有限公司企业考勤管理系统 一、系统目标设计 1系统开发的总体任务是实现企业员工考勤管理的系统化、规范化、和自动化。 2能够和人事管理系统、工资管理系统相结合，真正实现企业高效、科学、现代化的员工管理。 二、开发实际思想 1尽量采用公司现有软硬件环境，及先进的管理系统开发方案，从而达到充分利用公司现有资源，提高系统开发水平和应用效果的目的。 2员工考勤管理系统能够和考勤机相连接，从而完成自动、高效、科学的考勤信息输入。 3系统采用模块化程序设计方法，既便与系统功能的各种组合和修该，又便于未参与开发的技术维护人员补充、维护。 系统应具备数据库维护功能，即使根据用户需求进行数据的添加、删除、修改、被分等操作。 系统需求分析 1 考勤管理涉及企业人事管理的多个方面，如员工职务升迁、工资发放、奖金发放、员工医疗保险发放等等。本利自重的考勤管理系统需要完成功能主要有以下几点。 2 员工考勤信息处理。该莫完成员工考勤情况的输入、修改等操作。如果企业内有考勤机，可以将它的输出处理后，形成考勤管理系统考勤模块的
输入。 3 企业缺勤类型的设定。 4 企业考勤统计。该模块可对某个员工进行考勤情况的统计，生成统计报表。 5 缺勤时间，缺勤类型对工资的影响 6 缺勤时间，缺勤类型对升职的影响 数据字典 数据项 表1 名称员工编号 说明每个员工拥有唯一的编号 类型字符型 长度 4 有关数据存储员工基本信息存储 表2 名称员工姓名
信息学部 实验报告 课程名称：网络数据库 实验名称：游标、存储过程、触发器实验类型：□验证性□综合性□设计性实验日期： 学生姓名： 学号： 班级： 专业：信息管理与信息系统学部：信息学部 指导教师：康瑶 成绩： 评语： 2015 年—2016 年第二学期
实验五：游标、存储过程、触发器 实验内容： 在scott方案下完成如下实验内容： 1、用显式游标编写程序，程序的功能是：计算每一个部门的平均工资，输出部门名称dname及平均工资avg_sal。（利用scott方案下的emp表及dept表） 2、编写一个存储过程，此过程的功能是将scott方案下的emp表中工资大于1000的ename，job，sal的30%，comm插入表bonus中，要求用显示游标完成数据插入操作，然后编写PL/SQL程序执行此存储过程，并显示最终结果。 3、在scott方案下定义一个存储过程使用游标方式,根据emp表员工编号参数, 查询并打印该员工的下属的姓名,职位,工资等信息。 4、创建存储过程GetGrade（v_empno），查询指定员工的工资等级。工资小于等于3000，等级为“低”；工资大于3000，小于5000，等级为”中”；工资大于等于5000，等级为高。并执行该存储过程。 5、创建存储过程UpdateWage，查询指定员工的工资，如果工资小于3000， 则加200工资，并提示信息“XX号员工工资已更新。”，如果工资大于3000，则提示信息“XX号员工工资为XXX，已达到规定标准。” 6、定义一个函数,根据部门编号参数,查询出该部门的员工总数,并作为参数输出。 7、定义触发器MyTrigger，当表UserType中TypeId列的值发生变化时，自动更新表Users中的UserType列的值，从而保证数据的完整性： 8、创建触发器MyTrigger，它的作用是当表departments中的记录被删除后，自动删除表employees中的对应的员工记录，从而保证数据的完整性。 实验步骤（图文并茂）：
一、创建一个简单的触发器 触发器是一种特殊的存储过程，类似于事件函数，SQL Server? 允许为INSERT、UPDATE、DELETE 创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列T-SQL语句。触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。 创建触发器用CREATE TRIGGER CREATE TRIGGER 触发器名称 ON 表名 FOR I NSERT、UPDATE 或DELETE AS T-SQL 语句 注意：触发器名称是不加引号的。 如下是联机丛书上的一个示例，当在titles 表上更改记录时，发送邮件通知MaryM。CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE, DELETE AS EXEC master..xp_sendmail 'MaryM', 'Don''t forget to print a report for the distributors.' 二、删除触发器 用查询分析器删除 在查询分析器中使用drop trigger 触发器名称来删除触发器。 也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称... 注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在： if Exists(select name from sysobjects where name=触发器名称and xtype='TR') 用企业管理器删除 在企业管理器中，在表上点右键->“所有任务”->“管理触发器”，选中所要删除的触发器，然后点击“删除”。 三、重命名触发器 用查询分析器重命名 exec sp_rename 原名称, 新名称 sp_rename 是SQL Server? 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。
oracle11g 游标： 1. 当在PL/SQL中使用SQL语句时，Oracle会为其分配上下文区域，这是一段 私有的内存区域，用于暂时保存SQL语句影响到的数据。游标是指向这段内存区域的指针。 2. Oracle中主要有两种类型的游标: (1) 隐式游标:所有的DML语句和PL/SQL SELECT 语句都有; (2) 显式游标:由开发人员声明和控制。 3. 可以使用的游标属性包括四种：%ROWCOUNT、%FOUND、%NOTFOUND、 %ISOPEN，这四种属性对于显式游标和隐式游标都有用，但是含义和使用方法略有不同。游标在使用属性时，需要以游标名称作为前缀，以表明该属性是哪个游标的，隐式游标没有名称，所以在使用隐式游标时采取了统一的一个名称SQL。 4. 在PL/SQL中的SELECT语句只能且必须取出一行数据，取出多行或者零行都 被认为是异常，所以在对多行数据进行操作时，必须使用显式游标来实现。 5. 使用显式游标的步骤： (1)声明游标：CURSOR cursor_name is select_statement; (2)打开游标：OPEN cursor_name; (3)取游标中的数据：FETCH cursor_name INTO variable1,variable2,...; (4)关闭游标：CLOSE cursor_name; 6.用变量接收游标中的数据 sql> declare v_name emp.ename%TYPE; v_sal emp.sal%TYPE; cursor emp_cursor is select ename,sal from emp where deptno=10; begin open emp_cursor; loop fetch emp_cursor into v_name,v_sal; exit when emp_cursor%NOTFOUND; dbms_output.put_line(v_name || ‘的薪水是’ || v_sal);
--建立触发器，显示修改人数 create trigger d1 on jun for insert,update as select '你正在修改数据' declare @a varchar(20) select @a=str(@@rowcount)+'个学生被修改' select @a select * from jun return insert into jun(爱好) values('跑步') update jun set 爱好='打球' where 学号<='103' --建立触发器，当男生人数在6个以内可以加入，否则不能加入 create trigger i1 on zg for insert as if((select count(性别) from zg where 性别='男')>6) select '不能插入，男生人数已满。' else select'插入成功' select * from zg insert into zg(学号,姓名,性别) values(20100636,'江渝','男') --建立触发器，如果语文成绩在200以内可以修改，否则不能修改 create trigger u1 on zg for update as if((select max(语文) from zg )>200) begin select '你不能修改' rollback end else select '修改成功' select * from zg update zg set 语文=语文+10 where 姓名='唐荣强' update zg set 语文=语文-20 where 姓名='张军' update zg set 语文=语文+30 where 姓名='张军'
SQL触发器实例讲解 2008-11-27 16:16:45| 分类：PROCEDURE | 标签：|字号大中小订阅 SQL触发器实例1 定义：何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。 我为什么要使用触发器？比如，这么两个表： Create Table Student( --学生表 StudentID int primary key, --学号 .... ) Create Table BorrowRecord( --学生借书记录表 BorrowRecord int identity(1,1), --流水号 StudentID int , --学号 BorrowDate datetime, --借出时间 ReturnDAte Datetime, --归还时间 ... ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 等等。 这时候可以用到触发器。对于1，创建一个Update触发器： Create Trigger truStudent On Student --在Student表中创建触发器 for Update --为什么事件触发 As --事件触发后所要做的事情 if Update(StudentID) begin Update BorrowRecord Set StudentID=i.StudentID
删除SQL Server 的特定实例 若要删除SQL Server 的特定实例，请按照下列步骤操作： 1.找到并删除%drive%:\Program Files\Microsoft SQL Server\MSSQL\Binn 文件 夹，其中%drive%是要删除的SQL Server 实例的位置。 2.找到以下注册表项： o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer 注意：如果要删除默认实例，则必须删除除Client项以外的所有项。 o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server 必须使用Regedt32.exe 来编辑要删除的实例的InstalledInstances 值。默认实例的显示名称为MSSQLSERVER，而命名实例的显示名称则为给该 实例指定的名称。 注意：不能使用Regedit.exe 编辑该值；您必须使用Regedt32.exe。 注意：如果这是SQL Server 2000 的群集实例，请为群集中的每个节点重复执行这些步骤。 删除SQL Server 的所有已知实例 若要删除SQL Server 的所有已知实例，请按照下列步骤操作： 1.找到并删除%drive%:\Program Files\Microsoft SQL Server\MSSQL\Binn 文件 夹。 2.找到并删除以下注册表项： o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer o HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server 3.找到并删除以下注册表项： o HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL Server o HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSER VERAGENT o HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL ServerADHelper 4.步骤3 中的三个注册表项对应于Microsoft SQL Server 2000 的默认实例。命名实例 对应的注册表项与步骤 3 中列出的注册表项类似，但后面带有$instance_name，因
SQL触发器全过程 第一、概述 一：触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约`束。 二： SQL Server为每个触发器都创建了两个专用表：Inserted表和Deleted 表。这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后，与该触发器相关的这两个表也被删除。 Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。 Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。 三：instead of 和 After触发器 SQL Server2000提供了两种触发器：Instead of 和After 触发器。这两种触发器的差别在于他们被激活的同： Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外，Instead of 触发器也可以用于视图，用来扩展视图可以支持的更新操作。 After触发器在一个Insert,Update或Deleted语句之后执行，进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。
一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器，一个表的每个修改动作都可以有多个After触发器。 四：触发器的执行过程 如果一个Insert﹑update或者delete语句违反了约束，那幺After触发器不会执行，因为对约束的检查是在After触发器被激动之前发生的。所以After 触发器不能超越约束。 Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行，所以它可以对约束进行一些预处理。 五：使用T-SQL语句来创建触发器 基本语句如下： create trigger trigger_name on {table_name | view_name} {for | After | Instead of } [ insert, update,delete ] as sql_statement
SqlServer触发器的原理及案例合理的选用触发器会让你的系统更高效 2010 Ssc
目录 第1章何为触发器 (3) 1.1 触发器的'本质' (3) 1.2 这样做带来的'功能'： (3) 1.3 触发器的作用 (3) 1.4 说明： (3) 第2章对触发器3种操作的分析 (4) 2.1 创建触发器 (4) 2.2 学习案例 (5) 2.2.1 建立表 (5) 2.2.2 触发器练习1 (5) 2.2.3 触发器练习2： (6) 第3章图形化操作触发器 (7) 3.1 查看触发器情况 (7) 第4章触发器中的变量操作 (11) 第5章SQL触发器语法参考 (11)
第1章何为触发器 1.1触发器的'本质' 触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录、更改记录或者删除记录时，当事件发生时，才被自动地激活。 1.2这样做带来的'功能'： 触发器可以用来对表实施复杂的完整性约束，保持数据的一致性，当触发器所保护的数据发生改变时，触发器会自动被激活，响应同时执行一定的操作（对其它相关表的操作），从而保证对数据的不完整性约束或不正确的修改。触发器可以查询其它表，同时也可以执行复杂的T-SQL语句。触发器和引发触发器执行的命令被当作一次事务处理，因此就具备了事务的所有特征。 注意：'事务具备什么特征？在触发器中的作用？' 如果发现引起触发器执行的T-SQL语句执行了一个非法操作，比如关于其它表的相关性操作，发现数据丢失或需调用的数据不存在，那么就回滚到该事件执行前的SQL SERVER数据库状态。 1.3触发器的作用 触发器可以对数据库进行级联修改，这一点刚才已经说过了。需要说明的是：'触发器和约束的关系和区别' (1)一般来说，使用约束比使用触发器效率更高。 (2)同时，触发器可以完成比CHECK约束更复杂的限制。 1.4说明： 1.与CHECK约束不同，在触发器中可以引用其它的表。 2.触发器可以发现改变前后表中数据的不一致，并根据这些不同来进行相应的操作。
SQL Code 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Declare @barCode varchar(50)--声明变量用于存放游标结果 DECLARE CsrName CURSOR FOR--声明游标 select barCode from eachBook --游标数据来源 BEGIN OPEN CsrName --打开游标 FETCH FROM CURSUBOK INTO @barCode--获得来源的一行记录放入变量 WHILE @@FETCH_STATUS = 0 BEGIN -----------------------------------------------------针对每个结果做的操作 print @barcode ----------------------------------------------------------------------------End FETCH NEXT FROM CsrName INTO @barCode --处理下一行数据 CLOSE CsrName --游标用完了要关掉 END 总结：一般sql语句是面向集合的，游标是面向集合里面的行的，相当于获取一行数据->处理->获得下一行数据->处理这样一个循环。能不用游标就尽量不要用游标，性能很烂 --两种触发器，after为表sql语句执行之后操作，INSTEAD OF为之前操作 create trigger tgr_name--创建触发器 on classes --在classes表上 for insert--当classes表插入数据是触发as里面的操作(for 有after默认值，所以是sql 语句执行完了执行) --，有insert、update、delete三种操作类型。 as declare @id int;--定义变量用来临时存放数据 select @id = id from inserted; --在inserted表中查询已经插入记录信息，update就有个updated表，deleted就是deleted表 print '刚刚插入的记录的ID是'+@id; GO
SQL触发器语法参考 CreateTRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR |AFTER | INSTEAD OF } { [ Insert ] [ , ] [ Update ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF Update ( column ) [ { AND | or } Update ( column ) ] [ ...n ] | IF ( COLUMNS_UpdateD ( ) { bitwise_operator } updated_bitmask) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } } 参数 trigger_name 是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。 Table | view 是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。 WITH ENCRYPTION 加密syscomments 表中包含Create TRIGGER语句文本的条目。使用WITH ENCRYP TION 可防止将触发器作为SQL Server复制的一部分发布。 AFTER 指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。 如果仅指定FOR 关键字，则AFTER是默认设置。 不能在视图上定义AFTER 触发器。
SQL触发器实例讲解 SQL 资料2009-07-23 14:44:07 阅读6072 评论9 字号：大中小订阅 定义：何为触发器？在SQL Serv er里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。 我为什么要使用触发器？比如，这么两个表： Create Table Student( --学生表 StudentID int primary key, --学号 .... ) Create Table BorrowRecord( --学生借书记录表 BorrowRecord int identity(1,1), --流水号 StudentID int , --学号 BorrowDate datetime, --借出时间 ReturnDAte Datetime, --归还时间 ... ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 等等。 这时候可以用到触发器。对于1，创建一个Update触发器： Create Trigger truStudent On Student --在Student表中创建触发器 f or Update --为什么事件触发 As --事件触发后所要做的事情 if Update(StudentID) begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i --Deleted和Inserted临时表 Where br.StudentID=d.StudentID end
创建触发器 为了演示触发器的功能，下面再引入一个简易仓库管理的例子。在采购配件前，必须首先制定采购计划（如采购计划表 PlanA），然后交由采购员采购，采购回来后，要将配件入库，入库时，除了要修改入库表（配件入库表 InStock）外，还要修改配件库存表（仓库库存表 Stock），还要修改采购计划表（采购计划表 PlanA），因为要修改实际完成的采购量（FinishQty）。三个数据表如下： 采购计划表 PlanA 备件入库表 InStock 备件库存表 Stock 给计划表追加如下数据 一、用企业管理器创建触发器 例1：对备件入库InStock表建立一个插入触发器utr_InStockIns，其功能为：备件入库时，除了要将入库数据追加到备件入库表(InStock)外，还要修改计划表(PlanA)中对应计划号的完成数量(增加FinishQty)和备件库存表(Stock)中对应备件代码的库存数量(StockQty)。 注：同企业管理器创建的触发器
例2：对备件入库表建立一个删除触发器utr_InStockDel，其功能为：删除备件入库中的记录时，除了要删除入库表(InStock)的记录外，还要修改计划表(PlanA)中对应计划号的完成数量(减少FinishQty)，还要删除备件库存表(Stock)中对应备件代码的记录。
例3：对备件入库表建立一个更改除触发器utr_InStockUpt，其功能为：更改备件入库中的记录时，除了要更改入库表(InStock)的记录外，还要更改备件库存表(Stock)中对应备件代码的数据，同时，还要更改计划表(PlanA)中对应计划号的完成数量(FinishQty)。
SQLServer 触发器使用实例 触发器是一个特殊的存储过程。 常见的有三种：分别应用于Insert , Update , Delete 事件。 一、Trigger语法： 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 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一 样的，只是存放的数据有差异。 二、实例： 实例1（注意这里的关联，条件是如何关联，update中set的值是与谁关联。这些都可以从【触发器的两个临时表：inserted、deleted】中得到答案） 比如，这么两个表： Create Table Student( --学生表 StudentID int primary key, --学号 StudentName varchar(50),姓名 ) Create Table BorrowRecord( --学生借书记录表 BorrowRecord int identity(1,1), --流水号 StudentID int , --学号 BorrowDate datetime, --借出时间
数据库设计的案例分析 一、教学管理 1. 基本需求 某学校设计学生教学管理系统。学生实体包括学号、姓名、性别、生日、民族、籍贯、简历、登记照，每名学生选择一个主修专业，专业包括专业编号和名称，一个专业属于一个学院，一个学院可以有若干个专业。学院信息要存储学院号、学院名、院长。教学管理还要管理课程表和学生成绩。课程表包括课程号、课程名、学分，每门课程由一个学院开设。学生选修的每门课程获得一个成绩。 设计该教学管理的ER模型，然后转化为关系模型。 若上面的管理系统还要管理教师教学安排，教师包括编号、姓名、年龄、职称，一个教师只能属于一个学院，一名教师可以上若干门课程，一门课程可以有多名老师来上，每个教师所上的每门课都有一个课堂号和课时数。试修改上题的ER模型，将教师教学信息管理增加进去。
2. 参考设计： 图一教学管理ER图 由ER模型转换的关系模型是： 学生（学号，姓名，性别，生日，民族，籍贯，专业号，简历，登记照）专业（专业号，专业，专业类别，学院号） 学院（学院号，学院，院长） 课程（课程号，课程名，学分，学院号） 成绩（学号，课程号，成绩） （题目分析：本题中有学生、专业、学院、课程四个实体。一个学生只有一个主修专业，学生与专业有多对一的联系；一个专业只由一个学院开设，一门课程只由一个学院开设，学院与专业、学院与课程都是一对多的联系；学生与课程有多对多的联系。 在转换为关系模型时，一对多的联系都在相应的多方实体的关系中增加一个外键。） 增加教师，ER图如下。
图二有教师实体的教学管理ER图 3. 物理设计 基于Access的数据库结构设计如下。 指定数据库文件的名称，并为设计好的关系模型设计表结构。 数据库文件保存在“E:\教学管理\”文件夹中，数据库文件名：教学管理.MDB。 表包括：学院、专业、学生、课程、成绩单。对应表结构如表1-2至表1-6所示。 表1-1 学院 表1-2 专业 表1-3 学生
SQL Server 触发器 触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。 ?什么是触发器 触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。 DML触发器分为： 1、after触发器（之后触发） a、insert触发器 b、update触发器 c、delete触发器 2、instead of 触发器（之前触发） 其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。 触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数
据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。 对表的操作Inserted逻辑表Deleted逻辑表 增加记录（insert）存放增加的记录无 删除记录（delete）无存放被删除的记录修改记录（update）存放更新后的记录存放更新前的记录 Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。 ?创建触发器 语法 create trigger tgr_name on table_name with encrypion –加密触发器 for update... as Transact-SQL # 创建insert类型触发器 --创建insert插入类型触发器 if (object_id('tgr_classes_insert', 'tr') is not null) drop trigger tgr_classes_insert go create trigger tgr_classes_insert
触发器 ?触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化（INSERT、 UPDATE 或DELETE）时自动执行 ?触发器可以查询其它表，并可以包含复杂的Transact-SQL 语句 ?主要用于强制复杂的业务规则或要求 优点 ?自动执行 ?实现相关表层叠修改，实现多个表之间数据的一致性和完整性 ?实现比check约束更复杂的限制，可以引用其他表中的列 触发器的类型 ?AFTER触发器：在数据变动(INSERT、UPDATE、DELETE操作)完成后激发，只能在表 上定义，同一个表中可以有多个AFTER触发器 ?INSTEAD OF触发器：在数据变动以前被激发，并取代变动数据(INSERT、UPDATE、 DELETE操作)，转而去执行触发器定义的操作，可以定义在表或视图上，每个update、insert和delete语句最多可以定义一个INSTEAD OF触发器。 创建触发器 ?CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [, ][DELETE]} AS sql_statement [...n ] } 触发器示例： ?CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10) 指定触发器何时激发 ?AFTER 触发器在触发操作（INSERT、UPDATE 或DELETE）后和处理完任何约束后激 发。可通过指定AFTER 或FOR 关键字来请求AFTER 触发器。 ?INSTEAD OF 触发器代替触发动作进行激发，并在处理约束之前激发。 ?对于每个触发操作（UPDATE、DELETE 和INSERT），每个表或视图只能有一个 INSTEAD OF 触发器。而一个表对于每个触发操作可以有多个AFTER 触发器。 触发器示例： create TRIGGER reminder ON titles FOR INSERT, delete,update AS --修改操作 if (select count(*) from inserted) > 0 and (select count(*) from deleted) >0 begin RAISERROR ('修改成功', 16, 10) end
定义：何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。 我为什么要使用触发器？比如，这么两个表： Create Table Student( --学生表 StudentID int primary key, --学号 .... ) Create Table BorrowRecord( --学生借书记录表 BorrowRecord int identity(1,1), --流水号 StudentID int , --学号 BorrowDate datetime, --借出时间 ReturnDAte Datetime, --归还时间 ... ) 用到的功能有: 1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。 等等。 这时候可以用到触发器。对于1，创建一个Update触发器： Create Trigger truStudent On Student --在Student表中创建触发器 for Update --为什么事件触发 As --事件触发后所要做的事情 if Update(StudentID) begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i --Deleted和Inserted临时表Where br.StudentID=d.StudentID end 理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。