oracletrigger(oracle如何添加触发器)

一、oracle触发器如何使用

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。 1.说明 1)触发器是一种特殊的

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。

1.说明

1)触发器是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，存储器由语句块去调用

2)触发器分类：

1.DML触发器：创建在表上，由DML事件引发

2.instead of触发器：创建在视图上并且只能在行级上触发，用于替代insert，delete等操作(由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，，它是专门为进行视图操作的一种处理方法)

3.DDL触发器：触发事件时数据库对象的创建和修改

4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发

3)组成：

1.触发事件：引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句(如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件

(如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)。

2.触发时间：即该触发器是在触发事件发生之前(BEFORE)还是之后(AFTER)触发

3.触发操作：触发器触发后要完成的事情

4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。

5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。

6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。(比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次)

2.语法

1)说明

不同类型的触发器例如DML触发器，Instead of触发器，系统触发器语法格式区别较大

2)一般语法

CREATE [OR REPLACE] TIGGER触发器名触发时间触发事件

ON表名/视图名

[FOR EACH ROW]//加上FOR EACH ROW即为行级触发器，不加时为语句级触发器BEGINpl/sql语句

END

create [or replace] trigger [schema.]trigger_name

{before| after| instead of}

{delete [or insert][or update [of column,...n]]}

on [schema.]table_name| view_name

[for each row [when(condition)]]

sql_statement[,...n]

例如：

CREATE OR REPLACE TRIGGER trigger_name

ON table_name

[FOR EACH ROW]WHEN(condition)

DECLAREBEGINEND;

3)instead of触发器语法

语法：

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF{INSERT|DELETE|UPDATE [OF COLUMN...]}[OR{INSERT| DELETE| UPDATE [OF COLUMN...]}]ON VIEW_NAME[REFFERENCING{OLD [AS] OLD| NEW [AS] NEW| PARENT AS PARENT}]//可以指定相关名称，当前的默认相关名称为OLD和NEW,

应用相关名称时需要加：[FOR EACH ROW]//instead of触发器只能在行级上触发，因为没有必要指定[WHEN CONDITION]DECLARE

BEGINEND;

说明：INSTEAD OF用于对视图的DML触发，由于视图可能有多个表进行联结而成，因而并非所有的联结均可更新，运用 INSTEAD OF触发器可完成相应的操作。

3.实例

创建测试表格：

CREATE TABLE"HNZC"."TRIGGERTEST"

(

"ID" VARCHAR2(20 BYTE),

"NAME" VARCHAR2(20 BYTE),

"SCORE" NUMBER

);create table tab1 select* from triggertest;

1)DML触发器/行级触发器

触发器如下：

CREATE OR REPLACE TRIGGER TRIGGER1

AFTER INSERT ON TRIGGERTEST//插入后触发FOR EACH ROW//行级触发器BEGIN

INSERT INTO tab1(ID,NAME) VALUES('22','33');END;

执行语句：

insert into triggertest(id) values('aabbcc');

语句执行结束，表tab1中新增加一条数据

2)限制对表的修改(例如非工作时间不能修改某些表)

触发器如下：

CREATE OR REPLACE TRIGGER TRIGGER1

AFTER INSERT ON TRIGGERTEST

FOR EACH ROW

BEGIN

IF(TO_CHAR(SYSDATE,'DAY') IN('星期三','星期天')) THEN RAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest'); END IF;END;

执行语句：

insert into triggertest(id) values('aabbcc');

今天周三因而输出结果为：

在行 1上开始执行命令时出错:insert into triggertest(id) values('aabbcc')

错误报告:

SQL错误: ORA-20001:不是上班时间，不能修改表格triggertest

ORA-06512:在"HNZC.TRIGGER1", line 3ORA-04088:触发器'HNZC.TRIGGER1'执行过程中出错

通常对表的修改限制如下(即周一至周五9——18点能修改表格)

CREATE OR REPLACE TRIGGER TRIGGER1

BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

FOR EACH ROW

BEGIN

IF(TO_CHAR(SYSDATE,'DAY') IN('星期六','星期天')) OR(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN'9:00' AND'18:00') THEN RAISE_APPLICATION_ERROR(-20001,'不是上班时间，不能修改表格triggertest'); END IF;END;

3)增加限制条件(如不能更改某个员工的记录)

触发器如下：(如下实现月儿的分数只能增加)

CREATE OR REPLACE TRIGGER TRIGGER1

BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

FOR EACH ROW

WHEN(OLD.NAME='月儿')BEGIN

CASE WHEN UPDATING('SCORE') THEN

IF:NEW.SCORE

当前月儿的分数为20

当修改为10时出错

UPDATE"HNZC"."TRIGGERTEST" SET SCORE='10' WHERE ROWID='AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN='47685303'ORA-20001:月儿的分数只能提升不能下降

ORA-06512:在"HNZC.TRIGGER1", line 4ORA-04088:触发器'HNZC.TRIGGER1'执行过程中出错

当修改为30时成功

UPDATE"HNZC"."TRIGGERTEST" SET SCORE='30' WHERE ROWID='AAAdEzAAPAAAAH+AAB' AND ORA_ROWSCN='47685303'提交成功

4)在触发器中调用存储过程

触发器为：

CREATE OR REPLACE TRIGGER TRIGGER1

BEFORE INSERT OR DELETE OR UPDATE ON TRIGGERTEST

FOR EACH ROW

BEGIN

TESTPRO1();END;

存储过程为：

create or replacePROCEDURE TESTPRO1 AS BEGIN

insert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;

执行完毕后tab1中增加一条数据

5)级联更新

触发器如下(triggertest表中name修改时同时修改tab1中的name)

create or replacePROCEDURE TESTPRO1 AS BEGIN

insert into tab1(id,name,score) VALUES('AAA','BBB',200);END TESTPRO1;

执行语句：

update triggertest set name='水儿' where;

结果：tab1中name为月儿的也更改为水儿

6)instead of触发器

TABLE STUDENT表格数据如下

创建视图student_view

CREATE OR REPLACE VIEW STUDNET_VIEW

AS SELECT CLASSID,AVG(SCORE) AVERAGE_SCORE FROM STUDENTGROUP BY CLASSID;

视图数据如下：

对视图student_view执行如下操作：

DELETE FROM STUDNET_VIEW WHERE CLASSID='111';

执行结果：

错误报告:

SQL错误: ORA-01732:此视图的数据操纵操作非法01732. 00000-"data manipulation operation not legal on this view"

解决方法：创建INSTEAD OF视图

CREATE OR REPLACE TRIGGER STUDENT_VIEW_DELETE

INSTEAD OF DELETE ON STUDNET_VIEW

FOR EACH ROWBEGIN

DELETE FROM STUDENT WHERE CLASSID=:OLD.CLASSID;END STUDENT_VIEW_DELETE;

执行删除语句

DELETE FROM STUDNET_VIEW WHERE CLASSID='111';

执行结果：删除成功

1行已删除。

4.注意事项

1)在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE)，不能使用DDL语句(CREATE、ALTER、DROP)

2)触发器中不能使用commit语句，触发器的操作与触发事件(INSERT,UPDATE,DELETE)一起进行COMMIT和ROLLBACK;

3)一个表上的触发器越多，对于表的DML操作性能影响越大

4)触发器最大为32K

二、Oracle存储过程触发器用法

1：什么叫存储过程？

在oracle中，可以在数据库中定义子程序，这种程序块叫存储过程（procedure），它存放在数据字典中，可以在不同用户和应用程序中共享，并可以实现程序的优化和重用。

2：存储过程的有点？

过程运行在服务器端，执行速度快

过程执行一次后代码就驻留在高速缓存中，在以后的操作中，直接调用缓存数据。

确保数据库的安全性，可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问数据库的过程。

自动完成需要预先执行的任务，过程可以在系统启动时自动运行。、

3：存储过程的创建和执行

用户存储过程只能定义在当前数据库中，可以使用sql语句或OEM创建。

默认情况下，用户创建的存储过程归登录数据库的用户所拥有，DBA可以把许可授权给其他用户。

Sql命令创建存储过程

CREATEprocedure过程名

【（parameterparameter_modedate_type,….n）】

IS|AS

BEGIN

SQL_STATEMENT

END过程名

其中

Parameter表示过程的参数。

parameter_mode:参数的类型，过程参数和函数参数一样，有三种类型IN,OUT,INOUT

IN表示参数是输入给过程的。

OUT表示采纳数载过程中被赋值，可以传给过程的外部。

INOUT表示可以传内，可以传外。

调用存储过程

直接输入存储过程的名字可以执行一个已经定义的存储过程。

存储过程的编辑修改

修改存储过程虽然可以和修改视图一样，用ALTERprocedure，但是他是用来重新编译和验证用的，如果要修改过程定义，建议还是用createorreplace。

存储过程的删除

当某个过程不再需要的时候，应该将其从内存中删除，以释放它占用的资源。

Dropprocedure存储过程名。

Dropprocedureupdate_info.

触发器

触发器（trigger）是一些过程，与表联系紧密，用于保护表中的数据，当一个基表被修改时，触发器自动就会执行。例如出入其可以实现多个表间数据的一致性和完整性。

一般情况下，对表数据的操作有增删查改，维护数据的触发器也可以分为多种，每张基表最多可以建立12个触发器。

1:Beforeinsert2:beforeinsertfoeachrow;3:afterinert

4:afterinsertforeachrow5:beforeupdate6:beforeupdateforeachrow

7:afterupdate8:afterupdateforeachrow;9:beforedelete;

10:beforedeleteforeachrow11:afterdelete12:afterdeleteforeachrow.

利用sql语句创建触发器

语法规则

Createorreplacetrigger触发器名

{before|after|insteadof}

{delete【orinsert】【orupdate】【ofcolumn….n】}

On表名|视图名

【foreachrow】[]wherecondition]]

Sql_statement[,,,n]

参数说明

Insteadof:指定在创建替代触发器。

Ofcolumn指定在哪些列上进行触发。。。

创建触发器的限制

1：触发器中有效的语句可以包括DML语句，但是不能包括DDL，rollback，commit

,savepoint都不适用。

三、oracle 写一个触发器,新手

创建两个表：

create table a

(stdid int,

stdname varchar2(10));

create table b

(stdid int,

stdname varchar2(10));

创建触发器：

CREATE OR REPLACE TRIGGER tr_insert

after insert

ON a

FOR EACH ROW

BEGIN

INSERT INTO b(stdid,stdname)

VALUES(:new.stdid,:new.stdname);

END;

验证，在a表中插入数据：

insert into a values(1,'a');

commit;

验证b表结果：

如果业务需要在插入前作判断

就是说当且仅当表A插入的 stdid在表C中存在也就是说

表A新插入的的 stdid in select stdid from C时才执行下面插入表B的动作

创建个c表

create table c

(stdid int);

插入一条数据：

insert into c values(1);

commit;

触发器修改为：

CREATE OR REPLACE TRIGGER tr_insert

after insert

ON a

FOR EACH ROW

declare v_count int;

BEGIN

select count(*) into v_count from c where stdid=:new.stdid;

if v_count=0

then

INSERT INTO b(stdid,stdname)

VALUES(:new.stdid,:new.stdname);

end if;

END;

然后分别往a表中插入id=1和id=2的数据，剩下的自己验证吧，不给你截图了