有关sql insert触发器和update触发器

DML触发器有三类：

1， insert触发器；

2， update触发器；

3， delete触发器；

触发器的组成部分：

触发器的声明，指定触发器定时，事件，表名以类型

触发器的执行，PL/SQL块或对过程的调用

触发器的限制条件，通过where子句实现

类型：

应用程序触发器，前台开发工具提供的；

数据库触发器，定义在数据库内部由某种条件引发；分为：

DML触发器；

数据库级触发器；

替代触发器；

DML触发器组件：

1,触发器定时

2，触发器事件

3，表名

4， 触发器类型

5， When子句

6， 触发器主体

可创建触发器的对象：数据库表，数据库视图，用户模式，数据库实例

创建DML触发器：

Create [or replace] trigger [模式.]触发器名

Before| after insert|delete|(update of 列名)

On 表名

[for each row]

When 条件

PL/SQL块

For each row的意义是：在一次 *** 作表的语句中，每 *** 作成功一行就会触发一次；不写的话，表示是表级触发器，则无论 *** 作多少行，都只触发一次；

When条件的出现说明了，在DML *** 作的时候也许一定会触发触发器，但是触发器不一定会做实际的工作，比如when 后的条件不为真的时候，触发器只是简单地跳过了PL/SQL块；

Insert触发器的创建：

create or replace trigger tg_insert

before insert on student

begin

dbms_output.put_line('insert trigger is chufa le .....')

end

/

执行的效果：

SQL>insert into student

2 values(202,'dongqian','f')

insert trigger is chufa le .....

update表级触发器的例子：

create or replace trigger tg_updatestudent

after update on student

begin

dbms_output.put_line('update trigger is chufale .....')

end

/

运行效果：

SQL>update student set se='f'

update trigger is chufale .....

已更新8行；

可见，表级触发器在更新了多行的情况下，只触发了一次；

如果在after update on student后加上

For each row的话就成为行级触发器，运行效果：

SQL>update student set se='m'

update trigger is chufale .....

update trigger is chufale .....

update trigger is chufale .....

update trigger is chufale .....

update trigger is chufale .....

update trigger is chufale .....

update trigger is chufale .....

update trigger is chufale .....

已更新8行；

:new 与: old：必须是针对行级触发器的，也就是说要使用这两个变量的触发器一定有for each row

这两个变量是系统自动提供的数组变量，:new用来记录新插入的值，old用来记录被删除的值；

使用insert的时候只有:new里有值；

使用delete的时候只有:old里有值；

使用update的时候:new和:old里都有值；

可以这样使用： dbms_output.put_line('insert trigger is chufa

dbms_output.put_line('new id is : '||:new.stui

dbms_output.put_line('new name is : '||:new.st

dbms_output.put_line('new se is : '||:new.se)

可以这样从数据字典中查看一个表上有哪几个触发器：

SQL>select trigger_name from user_triggers

2 where table_name=upper('student')

TRIGGER_NAME

------------------------------

TG_INSERT

TG_UPDATESTUDENT

带有:old变量的行级delete触发器：

create or replace trigger tg_deletestudent

before delete on student

for each row

begin

dbms_output.put_line('old is: '||:old.stuid)

dbms_output.put_line('old name: '||:old.stuname)

end

/

运行效果：

SQL>delete from student

old is: 202

old name: dongqian

old is: 101

old name: liudehua

old is: 102

old name: lingqingxia

old is: 103

old name: lichanggong

old is: 104

old name: zhenxiuwen

old is: 1001

old name: lilianjie

old is: 1009

old name: tongleifuck

old is: 203

old name: kfdj

old is: 209

old name: fuck

已删除9行

When的使用：如果在begin也就是说触发器的PL/SQL主体块执行前加上when(old.se=’f’)的话，DML *** 作照做不误，但是只会在删除

Se=’f’的那行的时候才会执行触发器的主体动作，执行效果：

SQL>delete from student

old is: 209

old name: fuck

已删除9行； 这里虽然删了9行，但是只执行了一次触发器的主体，做为一个行级触发器；

混合类型触发器：

Inserting,deleting,updating三个谓词可以分别指示当前 *** 作到底是哪个；

create or replace trigger hunhetrigger

before insert or update or delete on student

for each row

begin

if inserting then

dbms_output.put_line('insert le.........')

end if

if deleting then

dbms_output.put_line('delete le .......')

end if

end

/

插入的时候就自动判断当前动作为插入：

SQL>insert into student values(303,'me','f')

insert le.........

删除的时候就自动判断当前动作为删除：

SQL>delete from student

delete le .......

注意，既然触发器内部的主体PL/SQL是语句，那么它同样也可以是插入删除 *** 作而不一定只是dbms_output打印一些信息；

这正是日志表的原理：在用户执行了DML语句的时候触发主体为插入日志表以记录 *** 作轨迹的触发器；

为什么用触发器？ 当我们有两个表用来记录商品的出库入库情况，good_store用来记录库存的产品类别和数量，

而good_out用来记录出库的产品类别和数量，那么每当我们出库的某个类别的产品一定数量的时候，我们应该在good_out中插入该产品的类别和

出库数量，而同时也应该在good_store表中用update来更新库存的相应类别的产品的数量；这就交给了我们两个必须完成的任务：插入good_out

表后更新good_store表，这样的手工过程使得我们觉得非常ugly,如果只做其中一个那造成数据的不一致；所以现在我们可以用触发器，在

Good_out表的插入 *** 作上绑定一个对good_store进行更新的触发器；当然这个过程应该是一个事务，你不必担心插入good_out表执行了，而绑定在这个动作上的触发器 *** 作不会执行，相信Oracle设计为原子性了；

注意：触发器会使得原来的SQL语句速度变慢；

替代触发器：

创建在视图上的触发器，就是替代触发器，只能是行级触发器；

为什么要用替代触发器？

假如你有一个视图是基于多个表的字段连接查询得到的；现在如果你想直接对着这个视图insert那你一定在想，我对视图的插入 *** 作

怎么来反应到组成这个视图的各个表中呢？事实上，除了定义一个触发器来绑定在对视图上的插入动作上外，你没有别的办法通过系统的报错而直接向视图中插入数据；这就是我们用替代触发器的原因；替换的意思实际上是触发器的主体部分把对视图的插入 *** 作转换成详细的对各个表的插入；

变异表：变异表就是当前SQL语句正在修改的表，所以在一个变异表上绑定的触发器不可以使用cout()函数，原因很简单：SQL语句刚刚修改了表，你怎么统计？？

约束表：

维护：

Alter trigger …..disenable 使得触发器不可用；

Alter trigger ……enable开启触发器；

Oracle的内置程序包

扩展数据库的功能；

为PL/SQL提供对SQL功能的访问；

一般具有sys权限的高级管理人员使用；

一个典型的程序包就是dbms_output,你老是用它的过程put_line()；

Dbms_standard 提供语言工具；

Dbms_lob *** 作Oracle LOB就是针对大型数据的 *** 作设计的；

Dbms_lock用户定义的锁；

Dbms_job 允许对PL/SQL过程进行调度；

Dbms_alert 支持数据库事件的异步通知；

1,dbms_output的一些过程：

a):enable

b):disable

c):put只是把数据放到缓存（SQL-Plus的缓存,实际就是整个窗口）中，无输出功能；

d):put_line可以使得以前放在缓存中所有数据输出；并且换到下一行；

e):new_line

f):get_line

g):get_lines

2，dmbs_lob ，这个包只能是由系统管理员来 *** 作；

Clob以字符数据存储可达2G；

Blob以二进制数据存储可达4G；

Nclob以unicode字符存储；

一个文件下载列表的例子：

创建下载目录表：

create table downfilelist

(

id varchar(20) not null primary key,

name varchar(40) not null,

filelocation bfile,

description clob

)

/

创建目录：

create or replace directory filedir as 'f:\oracle'

/只是向Oralce注册了目录，实际上并不会真的建立目录在磁盘上；Oracle无权管理和锁定 *** 作系统的文件系统；

向目录表中插入数据：

insert into downfilelist

values('10001','oracle plsal编程指南',bfilename(upper('filedir'),'demo.mp3'),'this is a mp3 music')

insert into downfilelist

values('10002','java 大权', bfilename(upper('filedir'),'x.jpg'),'good super girl')

/在filedir的目录f:\oracle下实际存储着demo.mp3 ,x.jpg

注意，如果你试图查询，效果是 ：

sys>select * from downfilelist

SP2-0678: 列或属性类型无法通过 SQL*Plus 显示

因为第三列是无法显示的，是一个二进制的；

下面使用dbms_lob的一些过程来进行 *** 作：

1,read过程

declare

tempdesc clob

ireadcount int

istart int

soutputdesc varchar(100)

begin

ireadcount:=5

istart:=1

select description into tempdesc from downfilelist where id='10001'

dbms_lob.read(tempdesc,ireadcount,istart,soutputdesc)把clob类型的tempdesc中的数据读到字符类型的soutputdesc里；

dbms_output.put_line('Top 5 character is: '||soutputdesc)

end

/注意，对unicode来说，汉字和字母所占的位数是一样的；

2，getlength函数

select description into tempclob from downfilelist where id=‘10001’

ilen:=dbms_lob.GetLength(tempclob)

append,copy……..

发现这样的现象：select x into y的时候，y并不是独立于x的拷贝，因为当修改y的时候x也被修改了；

3, fileexists函数

select id ，dbms_lob.fileexists(filelocation) from downfilelist

如果在bfile类型字段filelocation指定的系统下的目录中存在filelocation指定的系统文件，则返回int 1，否则返回0；

这说明Oracle还是可以检测到系统的文件情况的，如同java.io包里的类一样；

对bfile类型数据的 *** 作函数有fileisopen,fileopen,fileclose等等；

如果对您有帮助，请记得采纳为满意答案，谢谢！祝您生活愉快！

vaela

触发器是一种特殊类型的存储过程 它不同于存储过程 触发器主要是通过事件进行触发而被执行的 触发器的触发事件分可为 类 分别是DML事件 DDL事件和数据库事件 而存储过程可以通过存储过程名字而被直接调用 当对某一表进行诸如UPDATE INSERT DELETE 这些 *** 作时 SQL Server 就会自动执行触发器所定义的SQL 语句 从而确保对数据的处理必须符合由这些SQL 语句所定义的规则

触发器是特定事件出现的时候 自动执行的代码块 类似于存储过程 但是用户不能直接调用他们

触发器功能

允许/限制对表的修改

自动生成派生列 比如自增字段

强制数据一致性

提供审计和日志记录

防止无效的事务处理

启用复杂的业务逻辑

触发器种类

触发器的种类可划分为 种 数据 *** 纵语言(DML)触发器 替代(INSTEAD OF)触发器 数据定义语言(DDL)触发器 数据库事件触发器

数据 *** 纵语言(DML)触发器 简称DML触发器 是定义在表上的触发器 创建在表上 由DML事件引发的触发器 编写DML触发器时的两点要素是 确定触发的表 即在其上定义触发器的表 确定触发的事件 DML触发器的触发事件有INSERT UPDATE和DELETE三种替代触发器 简称INSTEAD　OF触发器 创建在视图上 用来替换对视图进行的删除 插入和修改 *** 作数据定义语言(DDL)触发器 简称DDL触发器 定义在模式上 触发事件是数据对象的创建和修改数据库事件触发器 定义在整个数据库或模式上 触发事件是数据库事件

ORACLE产生数据库触发器的语法为

CREATE [OR REPLACE] TRIGGER 触发器名

{BEFORE|AFTER|INSTEAD OF} 触发事件 [OR 触发事件 ]

ON 表名

WHEN 触发条件

[FOR EACH ROW]

DECLARE

声明部分

BEGIN

主体部分

END

其中

触发器名 触发器对象的名称 由于触发器是数据库自动执行的 因此该名称只是一个名称 没有实质的用途 一个触发器可由多个不同的数据 *** 纵语言 *** 作触发 在触发器中 可用INSERTING DELETING UPDATING谓词来区别不同的数据 *** 纵语言 *** 作 这些谓词可以在IF分支条件语句中作为判断条件来使用

触发时间 指明触发器何时执行 该值可取 触发的时间有BEFORE和AFTER两种 分别表示触发动作发生在DML语句执行之前和语句执行之后 确定触发级别 有语句级触发器和行级触发器两种 语句级触发器表示SQL语句只触发一次触发器 行级触发器表示SQL语句影响的每一行都要触发一次

Before 表示在数据库动作之前触发器执行在SQL语句的执行过程中 如果存在行级BEFORE触发器 则SQL语句在对每一行 *** 作之前 都要先执行一次行级BEFORE触发器 然后才对行进行 *** 作 如果存在行级AFTER触发器 则SQL语句在对每一行 *** 作之后 都要再执行一次行级AFTER触发器

after 表示在数据库动作之后出发器执行 如果存在语句级AFTER触发器 则在SQL语句执行完毕后 要最后执行一次语句级AFTER触发器

触发事件 指明哪些数据库动作会触发此触发器 指INSERT DELETE或UPDATE事件 事件可以并行出现 中间用OR连接

insert 数据库插入会触发此触发器

update 数据库修改会触发此触发器

delete 数据库删除会触发此触发器

表 名 数据库触发器所在的表

for each row 表示触发器为行级触发器 省略则为语句级触发器 对表的每一行触发器执行一次

触发器的创建者或具有DROP ANY TIRGGER系统权限的人才能删除触发器 删除触发器的语法如下

DROP TIRGGER 触发器名

可以通过命令设置触发器的可用状态 使其暂时关闭或重新打开 即当触发器暂时不用时 可以将其置成无效状态 在使用时重新打开 该命令语法如下

ALTER TRIGGER 触发器名 {DISABLE|ENABLE}

其中 DISABLE表示使触发器失效 ENABLE表示使触发器生效

同存储过程类似 触发器可以用SHOW ERRORS 检查编译错误

如果有多个触发器被定义成为相同时间 相同事件触发 且最后定义的触发器是有效的 则最后定义的触发器被触发 其他触发器不执行 触发器体内禁止使用MIT ROLLBACK SAVEPOINT语句 也禁止直接或间接地调用含有上述语句的存储过程 定义一个触发器时要考虑上述多种情况 并根据具体的需要来决定触发器的种类

触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性 除此之外 触发器还有其它许多不同的功能

( ) 强化约束(Enforce restriction)

触发器能够实现比CHECK 语句更为复杂的约束

( ) 跟踪变化Auditing changes

触发器可以侦测数据库内的 *** 作 从而不允许数据库中未经许可的指定更新和变化

( ) 级联运行(Cascaded operation)

触发器可以侦测数据库内的 *** 作 并自动地级联影响整个数据库的各项内容 例如 某个表上的触发器中包含有对另外一个表的数据 *** 作(如删除 更新 插入)而该 *** 作又导致该表上触发器被触发

( ) 存储过程的调用(Stored procedure invocation)

为了响应数据库更新触 发器可以调用一个或多个存储过程 甚至可以通过外部过程的调用而在DBMS( 数据库管理系统)本身之外进行 *** 作

由此可见 触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题 例如 触发器能够找出某一表在数据修改前后状态发生的差异 并根据这种差异执行一定的处理 此外一个表的同一类型(INSERT UPDATE DELETE)的多个触发器能够对同一种数据 *** 作采取多种不同的处理

总体而言 触发器性能通常比较低

lishixinzhi/Article/program/Oracle/201311/18340

---