转载 ORACLE 触发器

来源：互联网发布：http协议 mac地址编辑：程序博客网时间：2024/05/18 01:19

转自：http://apps.hi.baidu.com/share/detail/14611266

触发器是当特定事件出现时自动执行的代码块。
触发器与过程的区别在于：
过程是由用户或程序显式调用的，
而触发器是不能被直接调用的。Oracle会在事件请求触发器时，执行适当的触发器。

触发器可以用加强Oracle的默认功能，提供高度可定制的数据库。触发器能够执行
的功能有：
》自动生成数据
》强制复杂的完整性约束
》自定义复杂的安全权限
》提供审计和日志记录
》启用复杂的业务逻辑

触发器的一般语法：

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF}
{INSERT|DELETE|UPDATE[OF column[,column]...]}
[OR {INSERT|DELETE|UPDATE [OF column[,column]...]}]
ON [schema.]table_or_view_name
[REFERENCING [NEW AS new_row_name] [OLD AS old_row_name]]
[FOR EACH ROW]
[WHERE(condition)]
[DECLARE
variable_declation]
BEGIN
statements;
[EXCEPTION
exception_handlers]
END [trigger_name];

----------------
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual;
----------------
实例01：
CREATE OR REPLACE TRIGGER s_emp_trigger
BEFORE
INSERT OR DELETE OR UPDATE OF salary,name,id on s_emp
FOR EACH ROW
DECLARE
e_outtime EXCEPTION;
time_now varchar2(2);
time_upper varchar2(2) :='08';
time_lower varchar2(2) :='19';
BEGIN
time_now :=to_char(sysdate,'hh24');
IF time_now <time_upper or time_now>time_lower THEN
RAISE e_outtime;
ELSE DBMS_OUTPUT.PUT_LINE('修改数据库成功！！');
END IF;
EXCEPTION
WHEN e_outtime THEN
RAISE_APPLICATION_ERROR(-2222,'数据库在工作时间以外不允许修改！！');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出现未知异常！！');
END;
/

上面的触发器是限制在一天中8 点到19点以外的时间修改表 s_emp;
----------------
测试触发器
insert into S_EMP(ID,NAME,SALARY)
VALUES('5','yangguo','5000');

----------------
实例02：
CREATE OR REPLACE TRIGGER student_trigger
BEFORE
INSERT OR UPDATE OF name on student
FOR EACH ROW
BEGIN
:new.name:='wangyudong';
END;
/

----
上面的触发器是把要插入的值进行了更换，使得原先插入的name的value被修改为'wangyudong'
insert into student(id,name,class)
values('2','zhang','class2');
/

______________________________

触发器的组成部分：
1，触发器语句
触发器语句是那些可以导致触发器的事件，
既在表或者视图上执行的INSERT，DELETE和UPDATE之类的
DML(Data manipulation language 数据库操纵语言)语言，
在模式对象上执行的DDL(Data definition language
数据库定义语句)语句或数据库事件。
[insert|delete|update] of [column,[column..]]on [tableName |viewName]
or
[insert|delete|update] of [column,[column..]]on [tableName |viewName]
[FOR EACH ROW]//写的话是行级，不写就是语句级别

2，触发器限制
触发器限制条件包含一个boolean表达式，该值必须为真才能激活触发器。
如果该值为假或未知，将不会触发。
[WHEN(condition)]

3，触发器操作
触发器操作是触发器的主体，包含一些SQL语句和代码，这些代码在执行触发器语句
且触发器限制条件的值为真时运行。
行级触发器允许触发操作中的语句访问行的列值。
[DECLARE
variable_declation]
BEGIN
statements;
[EXCEPTION
exception_handlers]
END [trigger_name];

----------------------------------------------------------------
触发器类型：
1，行级别触发器
行级别触发器对DML语句影响的每个行执行一次。如果是UPDATE语句，可能影响
多行，也就多次执行触发器。
可以在CREATE TRIGGER命令中指定FOR EACH ROW子句创建行级别触发器

new.column 表示要插入更新的列的新值
old.column 表示要插入更新的列的旧值
:new.column := new_value 表示给新的列值变量赋值
用到new.column and old.column 一般在行级别触发器的 BEFORE 触发器里面使用

2，语句级别触发器
语句级别触发器对每一个DML语句执行一次。如果一个INSERT语句在表中插入200行，那么
在表上的INSERT语句级别触发器只执行一次。
语句级别的触发器以便不用于处理于数据相关的操作，通常用于强制实施在表上的安全
措施。语句级别触发器是CREAT TRIGGER命令创建的触发器的默认类型。

3，INSTEAD OF 触发器
INSTEAD OF 触发器是在视图上而不是在表上定义的触发器，它是用来替换所有使用实际语句
的触发器。这样的触发器可以用于克服Oracle在任何视图上设置的限制，允许拥护修改不能直接
修改的视图。
举例：
SET SERVEROUTPUT ON

CREATE OR REPLACE TRIGGER student_trigger
INSTEAD OF
INSERT OR UPDATE OF name on student_view
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('这视图是不允许被操作的！！');
END;
/

4，模式触发器
相当于是帐户级别触发器
在Oracle数据库中有多个帐户。
可以在模式级别的操作上建立触发器，如 CREATE，ALTER，DROP，GRANT，
REVOKE和 TRUNCATE 等DDL语句。
用户可以创建触发器类防止删除自己创建的表。
模式触发器提供的主要功能是阻止DDL操作以及在发生DDL操作的时候提供
额外的安全监控。
语法：
______________________________________
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE|AFTER}trigger_event
ON [schema.]SCHEMA
WHEN(trigger.condition)

trigger_body;
_______________________________________
实例：
(1)我们来创建一个表来存放删除表的信息：
create table dropped_object(
obj_name varchar2(30),
obj_type varchar2(30),
drop_date DATE
);

(2)我们来创建一个模式级别的触发器。用来记录被删除的对象
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO dropped_object(obj_name,obj_type,drop_date)
values(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);
END;

5，数据库级别触发器
可以创建在数据库事件上的触发器，包括启动，关闭，服务器错误，登陆和注销等。
这些都是实例范围的，不与特定的表或视图关联。可以使用这种类型的触发器自动进行
数据库的维护和审计活动。
语法-例子：
CREATE OR REPLACE TRIGGER system_startup
AFTER STARTUP ON DATABASE
BEGIN
--Do something
END;
/

_______________________________________________________________
_______________________________________________________________
启用和禁用触发器
触发器一旦创建，默认就立即生效。我们也可以手动将触发器停止或者启动。

起用或者禁用已知道的触发器
语法：
ALTER TRIGGER trigger_name {ENABLE|DISABLE};

起用或者禁用在特定表上建立的所有触发器
语法：
ALTER TABLE table_name {ENABLE|DISABLE} ALL TRIGGERS;

查看有关触发器的信息

DESC USER_TRIGGERS;

SELECT TRIGGER_NAME FROM USER_TRIGGERS;

删除触发器
语法：
DROP TRIGGER <trigger_name>;

___________________________________________________________
___________________________________________________________

ORACLE 内置程序包

内置包列表

_________________________
DBMS_OUTPUT程序包
DBMS_OUTPUT.ENABLE[(buffer_size)]
ENABLE过程用来起用对PUT，PUT_LINE和NEW_LINE等过程的调用，它
只有一个输入参数，即缓冲区大小(BUFFER_SIZE)。
缓冲区默认2000Byte,最小2000Byte,最大1000,000Byte

DBMS_OUTPUT.DISABLE
DISABLE没有输入和输出。DISABLE用于禁用对put put_line,new_line的调用.
调用DISABLE还可以清除环存里边的数据。

DBMS_OUTPUT.PUT(value)
我们输入一个参数，此参数被重载接受vachar2,number,date值。put用于在环存中
写入一条消息。注意，没有结束标记

DBMS_OUTPUT.PUT_LINE(value)
我们输入一个参数，它给我们输出，并且换行。里面有结束标记

DBMS_OUTPUT.NEW_LINE
没有参数，它的调用使得缓存中的东西被写到界面并且换行。

DBMS_LOB程序包
省略

DBMS_XMLQUERY包
省略

DBMS_RANDOM包
产生随即数。
举例：
declare
value number;
begin
for i in 1..20 loop
value:=dbms_random.random;
DBMS_OUTPUT.PUT_LINE(value);
end loop;
end;
/
要说明的是产生的数很随即，有正有负。大小不定。
如果要说明产生的随机数是1到100的，那应该怎么办？？
可以先用100求余 value:=MOD(value,100);
然后求绝对值 value:=ABS(value);
修改后：
declare
value number;
begin
for i in 1..20 loop
value:=dbms_random.random;
value:=MOD(value,100);
value:=ABS(value);
DBMS_OUTPUT.PUT_LINE(value);
end loop;
end;
/

DTL_FILE包
忽略

转自：http://apps.hi.baidu.com/share/detail/14611266