触发器与job的配合使用oracle,oracle各种触发器和使用规则

触发器是当特定事件出现时自动执行的代码块.触发器与过程的区别在于:过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的.

触发器能够执行的功能有:

1.自动生成数据.

2.强制复杂的完整性约束条件.

3.自定义复杂的安全权限.

4.提供审计和日志记录.

5.启用复杂的业务逻辑.

触发器的类型有:

1>行级触发器.(行级触发器对DML语句影响的每个行执行一次)

实例一:

--触发器

--行级触发器

create table test(sid number,sname varchar2(20));--创建一个表

create sequence seq_test;--创建序列

create or replace trigger tri_test--创建触发器

before insert or update of sid

on test

for each row--触发每一行

begin

if inserting then

select seq_test.nextval into:new.sid from dual;

else

raise_application_error(-20020,'不允许更新ID值!');--中断程序

end if;

end;

--测试,插入几条记录

insert into test values(0,'ff');

insert into test values(0,'ff');

insert into test values(0,'tt');

输出结果如下图所示:

实例二:

--创建一个触发器,无论用户插入新记录,还是修改emp表的job列,都将用户指定的job列的值转换成大写.

create or replace trigger trig_job

before insert or update of job

on emp

for each row

begin

if inserting then

:new.job:=upper(:new.job);

else

:new.job:=upper(:new.job);

end if;

end;

2>语句级触发器.(语句级触发器对每个DML语句执行一次)

实例:

create or replace trigger tri_test

after insert or update or delete

on test

begin

if updating then

dbms_output.put_line('修改');

elsif deleting then

dbms_output.put_line('删除');

elsif inserting then

dbms_output.put_line('插入');

end if;

end;

3>instead of 触发器.(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.)

语法如下:

create or replace trigger trig_test

instead of insert or update on 表名

referencing new as n

for each row

declare

..........

begin

........

end;

4>模式触发器.可以在模式级的操作上建立触发器.

实例如下:

create or replace trigger log_drop_obj

after drop on schema

begin

insert into .....

end;

5>数据库级触发器.

可以创建在数据库存事件上的触发器,包括关闭,启动,服务器错误,登录等.这些事件都是实例范围的,不与特定的表或视图关联.

实例:

create or replace trigger trig_name

after startup on database

begin

...........

end;

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

创建触发器:

CREATE [OR REPLACE] TRIGGER

BEFORE|AFTER

INSERT|DELETE|UPDATE [OF ] ON

[FOR EACH ROW]

WHEN ()

关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发;

关键字"FOR EACH ROW"指定触发器每行触发一次.

关键字"OF " 不写表示对整个表的所有列.

WHEN ()表达式的值必须为"TRUE".

特殊变量:

:new --为一个引用最新的列值;

:old --为一个引用以前的列值;

这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;

使用RAISE_APPLICATION_ERROR

语法:RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|false}]);

抛出用户自定义错误.

如果参数为'TRUE',则错误放在先前的堆栈上.

INSTEAD OF 触发器

INSTEAD OF 触发器主要针对视图(VIEW)将触发的dml语句替换成为触发器中的执行语句，而不执行dml语句.

禁用某个触发器

ALTER TRIGGER DISABLE

重新启用触发器

ALTER TRIGGER ENABLE

禁用所有触发器

ALTER TRIGGER DISABLE ALL TRIGGERS

启用所有触发器

ALTER TRIGGER ENABLE ALL TRIGGERS

删除触发器

DROP TRIGGER

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

1.触发器的创建规则：

①作用范围清晰；

②不要让触发器去完成Oracle后台已经能够完成的功能；

③限制触发器代码的行数；

④不要创建递归的触发器；

⑤触发器仅在被触发语句触发时进行集中的，全局的操作，同用户和数据库应用无关。

2.可以创建被如下语句所触发的触发器：

①DML语句(DELETE，INSERT，UPDATE)；

②DDL语句(CREATE，ALTER， DROP)；

③数据库操作(SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN)。

3.注意事项

①触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELETE 操作)触发；

②一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次；

③SELECT 并不更改任何行，因此不能创建 SELECT 触发器.这种场合下规则和视图更适合；

④触发器和某一指定的表格有关，当该表格备删除时，任何与该表有关的触发器同样会被删除；

⑤在一个表上的每一个动作只能有一个触发器与之关联；

⑥在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器；

4.删除触发器的语句格式为：

DROPTRIGGERnameONtable;

一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句，可为对一指定表的INSERT、UNPDATE或DELETE语句。触发限制是指定一个布尔表达式，当触发器激发时该布尔表达式是必须为真。触发器作为过程，是PL/SQL块，当触发语句发出、触发限制计算为真时该过程被执行。