oracle学习笔记(第十一章:触发器)

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON {[schema.] table_name | [schema.] view_name}
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
Declare
-- 变量;
Begin
-- trigger_body;
End ;

用途﹕
--1﹐强制执行 business rule.
--2﹐更新其他 table 中的数据.
--3﹐标示数据列以供处理.
--4﹐以信号通知已发生事件
--注意事项﹕
--1﹐不能使用rollback, commit ,savepoints,transaction交易控制
--2﹐不能使用long , long raw 变量
--3,after 的方式比 before 有效率
--4,行级触发器不可对 trigger 上的本表select,insert,update,delete操作
--5,:new,:old,:parent关键词只可以用在行级触发器中[for each row]
--6,:new,:old,关键词前的冒号只能在触发器体内有效.即在触发器体外不需要冒号,例如在when子句中
--7,:new,:old,伪记录,但是定义的 rowtype 类型的变量不能接受:new,或:old,也不能将:new或:old传递到接受rowtype类型的
--8, 代码大小必须小于32KB
old修饰访问操作完成前列的值﹐new修饰访问操作完成后列的值
 利用 new 与 old 来参考数据域的值
:OLD :NEW
delete 被删除的值 NULL
insert NULL 被建立的值
update 旧值 更改后的新值
 在 begin 中使用 , 须加 :
在 when 与 referencing 不须加 :
过程或函数中
--8,触发器数据字典:user_triggers
--9,变异表(mutating table) 和约束表(constrainting table)变异表是当前被DML语句修改的表,对触发器来说变异表就是在其上定义的表.
---由于执行delete cascade引用完整性约束蚖搨n更新的表也是变异表约束表是一种需要实施引用完整性约束而读入的表.
--10,触发器中的SQL语句不能进行下列操作:
-----读或修改触发语句的任何变异表,包括触发表本身
-----读或修改触发表的约束表中的主键,外键,唯一列
--11,由于在 行级触发器中不能在触发表上使用select,update,delete,update语句,
-----但可以使用语句级触发器,但是如果要使用:new,:old关键词,就必须使用行级
-----触发器,为了既可以使用:new,:old关键词,有可以在触发表上使用select,update,
-----delete,update语句我们可以在一个表上定义两个触发器:一个行级触发器,一个
-----语句级触发器,在行家触发器中使用:new,:old关键词,在语句级中使用select,update,insert,delete语句
-----至于如何记录:new的值,我们可以使用package,并在package中使用table数据类型
--12,行级触发器和语句级触发器的触发先后顺序:语句级别前,行级级别前,行级级别后,语句级别后
--13,有 for eache row 关键词的为 行级触发器，否则是语句级触发器
--选择性启动 TRIGGER﹐使用关键词deleting,updating,inserting
--如果是因为delete而执行trigger﹐则返回true
--如果是因为update而执行trigger﹐则返回true
--如果是因为insert而执行trigger﹐则返回true

create or replace trigger empm_trigger--------------定义一个选择性的触发器
before insert or update or delete on
on empm -–变异表
for each row -–行级触发器
declare
vemp_no varchar2(10);
begin
if deleting then
delete from empd where emp_no=:old.emp_no;
dbms_output.put_line('before deleting');
elsif updating then dbms_output.put_line('before updating');
elsif inserting then
dbms_output.put_line('before inserting');
end if;
end;

例2
-- when子句在trigger中使用
-- 语法:when trigger_condition
CREATE OR REPLACE TRIGGER t_when_empm
AFTER INSERT OR UPDATE OR DELETE
ON empm
FOR EACH ROW
WHEN(NEW.age >= 60 OR old.age >= 60) --在when子句中new前不需要加冒号
DECLARE
-- 声明变量
BEGIN
IF inserting THEN
INSERT INTO test VALUES(:new.emp_no,'insert',:new.age);
ELSIF updating THEN
INSERT INTO test VALUES(:new.emp_no,'update',:new.age);
ELSE
INSERT INTO test VALUES(:old.emp_no,'delete',:old.age);
END IF;
END;

--使触发器加强安全性:when()表示什么时间触犯
--使用when关键词﹐触发器触发前必须满足when条件
--下面的例子功能是﹕在上午11点之后向empm表插入的数据的sex列都为'1'
create or replace trigger empm_trigger5
before insert
on empm
for each row
when(to_number(to_char(sysdate,'HH24'))<11)
begin
:new.sex:='1'; --强制性的将 sex 设为 ‘1’
end;

referencing 子句在trigger中使用
-- 语法:REFERENCING [old as old_name] [new as new_name]
CREATE OR REPLACE TRIGGER t_referecing_empm
AFTER INSERT OR UPDATE OR DELETE
ON emp_m
REFERENCING NEW AS new_emp-- 在REFERENCING子句中new前不需要加冒号,该子句需要放在 for each row 之前
-- 使用REFERENCING子句后,可以用'引用'代替new或old
FOR EACH ROW
DECLARE

BEGIN
IF updating OR inserting THEN
UPDATE emp_d SET emp_email='referencing'
WHERE emp_no = :new_emp.emp_no;
END IF;
END;


--系统触发器
创建系统触发器的格式﹕
create or replace trigger trigger_name
{before | after}
{ddl_event_list | database_event_list}
on {database | [schema_name.]schema}
[when_claus]
trigger_body
说明﹕ddl_event_list﹕表示一个或多个ddl事件﹐事件之间用or分开
database_event_list﹕表示一个或多个数据库事件﹐事件之间用or分开
database﹕表示是数据库级别触发器
schema﹕表示是用户方案
trigger_body﹕表示触发器的PL/SQL语句

--创建当一个用户USERA登录时自动记录一些信息的触发器
create table userLog
(
userLogCount number(4,0) primary key ,
userID varchar2(15) not null,
userPSW varchar2(15) not null,
loginTime date not null,--登录时间
logoutTime date not null,--下线时间
logCount number(4) not null --登录次数
);

create trigger userlogTrigger
after logon on schema
begin
insert into userLog values(1,'userA','123456','2007-6-9','2009-9-8',3);
end loguser;

--建立一个记录创建表信息的表
create table ddl_creation
(
user_id varchar2(30),
object_type varchar2(20),
object_name varchar2(20),
object_owner varchar2(20),
creation_date date
);
--创建一个能记录创建表的相关信息的触发器
create or replace trigger create_table_info
after create on schema
begin
insert into ddl_creation(user_id,object_type,object_name,object_owner,creation_date)
values(user,sys.dictionary_obj_type,sys.dictionary_obj_name,sys.dictionary_obj_owner,sysdate);
end;


-- 建立一个记录用户登录数据库的系统triggr
create table jax_log_table
(
username varchar2(20),
log_time date,
onoff varchar(6),
address varchar2(30)
);

create trigger tr_logon
after logon on database
begin
insert into jax_log_table values(ora_login_user,sysdate,'logon',ora_client_ip_address);
end;

create trigger tr_logoff
before logoff on database
begin
insert into jax_log_table values(ora_login_user,sysdate,'logoff',ora_client_ip_address);
end;

--DDL触发器记录系统所发生的DDL事件（create，alter，drop等） 9i 以上的数据库可用
create table jax_event_ddl_table(event varchar2(20),
username varchar2(10),
owner varchar2(10),
objname varchar2(20),
objtype varchar2(10),
time date);

create trigger tr_ddl
after ddl on database
begin
insert into jax_event_ddl_table values(ora_sysevent,ora_login_user,
ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate)
end;

-- 常用系统变量
Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_des_encrypted_password 返回des加密后的用户口令
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
Ora_dict_obj_name_list(name_list out ora_name_list_t) 返回在事件中被修改的对象名列表
Ora_dict_obj_owner 返回ddl操作所对应的对象的所有者名
Ora_dict_obj_owner_list(owner_list out ora_name_list_t) 返回在事件中被修改的对象的所有者列表
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
Ora_grantee(user_list out ora_name_list_t) 返回授权事件的授权者
Ora_instance_num 返回例程号
Ora_is_alter_column(column_name in varchar2) 检测特定列是否被修改
Ora_is_creating_nested_table 检测是否正在建立嵌套表
Ora_is_drop_column(column_name in varchar2) 检测特定列是否被删除
Ora_is_servererror(error_number) 检测是否返回了特定oracle错误
Ora_login_user 返回登录用户名
Ora_sysevent 返回触发器的系统事件名。

--创建替代触发器 创建在视图上，只能是行级的触发器
CREATE OR REPLACE TRIGGER t_triggername
INSTEAD OF UPDATE OR INSERT OR DELETE
ON view_name
FOR EACH ROW
DECLARE
-- 声明变量;
BEGIN
-- 代码块 ;
END ;


--更改触发器的状态﹐
--方法一﹕alter trigger﹕alter trigger trigger_name disable(enable)
--方法二﹕alter table: alter table table_name disable(enable) all triggers