关于 SQL 触发器简单教程（一） [复制链接]

sunyesy

Senior Member[资深会员]

Rank: 3 Rank: 3

荣誉: 0 点
金币: 1411 个
注册时间: 2007-4-18
阅读权限: 200
精华: 1
积分: 123
帖子: 181

发表于 2007-11-3 14:05:29 |显示全部楼层

刚刚看到有人说触发器不是太明白，我在这里就班门弄斧的稍微谈谈自己的看法：
先引用一段关于触发器的说明（最好慢慢看完，如果这个说明你都不原意拜读，那我就没办法了），
在针对易飞ERP中的触发器做几个举例说明。
=============================================================================
触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。（定义）

FOR CREATE TRIGGER 语句
FOR 关键字之后可以跟 INSERT、UPDATE、DELETE 中的一个或多个，也就是说在其它情况下是不会触发触发器的，包括 SELECT、TRUNCATE、WRITETEXT、UPDATETEXT。

TRUNCATE TABLE 和不带 WHERE 的 DELETE 功能是一样的，都是删除表中的所有数据，不过 TRUNCATE TABLE 速度更快，占用的日志更少，这是因为 TRUNCATE TABLE 直接释放数据页并且在事务日志中也只记录数据页的释放，而 DELETE 是一行一行地删除，在事务日志中要记录每一条记录的删除。

那么可不可以用 TRUNCATE TABLE 代替不带 WHERE 的 DELETE 呢？在以下情况是不行的：
1、要保留标识的情况下不能用 TRUNCATE TABLE，因为 TRUNCATE TABLE 会重置标识。
2、需要使用触发器的情况下不能使用 TRUNCATE TABLE ，它不会激发触发器。
3、对于由 FOREIGN KEY 约束引用的表（即主键所在的表，不是外键所在的表）不能使用 TRUNCATE TABLE。
4、对于参与了索引视图的表不能使用 TRUNCATE TABLE ，注意指索引视图，并非普通视图。

不能在触发器中使用的语句

触发器中可以使用大多数 T-SQL 语句，但如下一些语句是不能在触发器中使用的。
CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。
ALTER 语句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。
DROP 语句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。
DISK 语句，如：DISK INIT、DISK RESIZE。
LOAD 语句，如：LOAD DATABASE、LOAD LOG。
RESTORE 语句，如：RESTORE DATABASE、RESTORE LOG。
RECONFIGURE
说明：有人说不能用 TRUNCATE TABLE 语句，其实是可以的。

查看某个触发器的内容

use 数据库名
go
exec sp_helptext '触发器名称'
将会以表的样式显示触发器内容。
除了触发器外，sp_helptext 还可以显示规则、默认值、未加密的存储过程、用户定义函数、视图的文本
用企业管理器查看

或者在表上点右键->“所有任务”->“管理触发器”，选择所要查看的触发器

查看当前数据库中有哪些触发器

在查询分析器中运行：

use 数据库名
go
select * from sysobjects where xtype='TR'
sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。

删除触发器

用查询分析器删除
在查询分析器中使用 drop trigger 触发器名称来删除触发器。
也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称...
注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：
if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')

用企业管理器删除
在企业管理器中，在表上点右键->“所有任务”->“管理触发器”，选中所要删除的触发器，然后点击“删除”。

重命名触发器
用查询分析器重命名
exec sp_rename 原名称, 新名称
sp_rename 是 SQL Server? 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。

用企业管理器重命名
在表上点右键->“所有任务”->“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。

查看触发器的属性

存储过程 sp_helptrigger 用于查看触发器的属性。

sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

例：

use 数据库名
go
exec sp_helptrigger tbl

触发器更多语法

INSTEAD OF
执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例：
create trigger f
on tbl
instead of delete
as
insert into Logs...

IF UPDATE(列名)
检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：
create trigger f
on tbl
for update
as
if update(status) or update(title)
sql_statement --更新了 status 或 title 列

inserted、deleted
, F; k2 B9 z+ q0 Q$ ?, e) L0 u这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：
create trigger tbl_delete
on tbl
for delete
as
declare @title varchar(200)
select @title=title from deleted
insert into Logs(logContent) values('删除了 title 为：' + title + '的记录')
说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。

慎用触发器
触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。

在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作，比如删除 T1 表记录时期望删除 T2 表相关的记录，此时可以建立级联删除的关系，也可以为 T1 表建立触发器使同时删除 T2 表相关记录，也可以自定义存储过程删除 T1 和 T2 表的记录，也可以在应用程序中使用两个 SQL 语句来删除……到底用哪一种好呢，应该说我们建立通过建立关系来实现级联删除是最好的，除非更有高的需求。

触发器还有一个用途可以用来保障数据的完整性，但同时规则、约束、默认值也可以保障数据完整性，到底哪一个好呢，一般说来，较为简单的完整性要求我们不应该使用触发器。两者在运行机制上也是有区别的，像规则、约束、默认值这些是在数据更改之前进行数据验证，而触发器是在数据更改之后进行验证（如果事务回滚，该表将不会产生变化）。

总之，如果我们对触发器过分的依赖，就会造成遍地是程序的情况，因为触发器本身就需要别的程序给它一个触发条件，也就是说至少在两个地方存在着程序，同时我们抛弃了约束、默认值等而选用触发器，势必影响数据库的结构。
=============================================================================
上面重点的部分我加了红色的标示。

具体到我们用的易飞ERP其实也不是难事：
随便举个例子：
例：我希望用户在新增品号信息的同时，把这个品号的品名（MB002）更新到商品描述（MB009）字段当中。
CREATE TRIGGER MB009_Insert --MB009_Insert 触发器的名称
ON [INVMB]                   --INVMB 数据表
FOR INSERT                   --关于INSERT 啥意思看上面的说明：）
AS
DECLARE @MB001 VARCHAR(20) --定义变量
UPDATE INVMB SET
MB009 = INVMB.MB002
FROM INVMB INNER JOIN INSERTED --INSERTED 啥意思在上面说明里面也写的很清楚了我不再熬述
ON INVMB.MB001=INSERTED.MB001

其他的也都是大同小异，会不会关键是看自己愿不愿意学了。

[ 本帖最后由 sunyesy 于 2007-11-3 14:20 编辑 ]

4 查看全部评分

帐号		自动登录	找回密码
密码			注册

关于 SQL 触发器 简单教程（一） [复制链接]

回复 1# 的帖子

关于 SQL 触发器简单教程（一） [复制链接]