一.oracle触发器介绍sql
触发器在数据库里以独立的对象存储，它与存储过程和函数不一样的是，存储过程与函数须要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。而且，触发器不能接收参数。因此运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操做或对视图进行相似的操做。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。因此触发器经常使用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各类操做，实现审计的功能。数据库
二.触发器语法安全
触发器由触发事件，触发条件和触发操做三部分组成。oracle
(1)触发事件app
指引发触发器被触发的SQL语句，数据库事件或者用户事件。ide
具体触发事件：函数
启动和关闭数据库实例spa
oracle错误消息对象
用户登陆和断开会话事件
特定表和视图的DML操做
在任何方案上的DDL操做
(2)触发条件(可选)
指使用when子句指定一个BOOLEAN表达式，当布尔表达式为TRUE时，会自动执行触发器相应代码。
(3)触发操做
指包含SQL语句和其余执行代码的PL/SQL块。
限制条件：
1.触发器代码不能超过32K。
2.触发器代码中不能使用long or long raw数据类型。
###基于视图的触发器instead of触发器
###DML触发器包括语句触发器和行触发器
DML须要指定触发时机(before|after),触发事件(insert,update,delete)，表名，触发类型，触发条件，触发操做。
触发时机:用于指定触发器的触发时间。before表示执行DML操做以前触发触发器。
触发事件:用于指定致使触发器执行DML操做。
表名：用于指定DML操做所对应的表。
触发类型：用于指定当触发事件发生以后，须要执行几回触发操做。
触发条件：用于指定执行触发器的条件，只有条件为TRUE时才会执行触发器代码。
触发操做：用于指定触发器执行代码。
三.触发器类型
1.语句触发器
当审计DML操做，或者确保DML操做安全执行时，可使用语句触发器。语句触发器不能记录列数据的变化。
语法：
create or replace trigger trigger_name
timing event1 [or event2 or event3]
on table_name
pl/sql block;
(1)before语句触发器
//禁止工做人员在周一改变雇员信息
create or replace trigger tr_change_emp
before insert or update or delete
---insert update delete三种操做触发触发器
on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') IN ('MON') then
---自定义错误输出
raise_application_error(-20001,'You can not change employee infor.');
end if;
end;
当触发器中同时包含多个触发事件时(insert,update,delete).区分具体的触发事件，可使用3个为此：
inserting,updating,deleting
create or replace trigger tr_change_emp
before insert or update or delete
on emp
begin
if to_char(sysdate, 'DY', 'nls_date_language=AMERICAN') IN ('MON') then
case
when inserting then
raise_application_error(-20001, 'You can not insert employee infor.');
when updating then
raise_application_error(-20002, 'You can not update employee infor.');
when deleting then
raise_application_error(-20003, 'You can not delete employee infor.');
end case;
end if;
end;
(2)after语句触发器
//审计emp表上insert,update,delete操做次数
思路
1.首先建立一个统计表t_audit，用于统计针对emp表的insert，update，delete次数
2.统计表中的第一个字段names，能够支持对多个表进行统计，每一个表为一行，有insert次数，update次数，delete次数
3.添加if语句检测，若是某个表是首次进行统计，则在统计表中新建一条记录，并把此表的各项次数初始化为0
---首先建立表
create table t_audit(names varchar2(30) not null primary key,insert_count number,update_count number,delete_count number,start_date date,end_date date);
---建立触发器
create or replace trigger tr_audit_emp
after insert or update or delete on emp
---on emp代表针对emp表有效
declare
v_tmp number;
begin
---
select count(*) INTO v_tmp from t_audit where names = 'EMP';
if v_tmp = 0 then
insert into t_audit values ('EMP', 0, 0, 0, sysdate, null);
end if;
case
when inserting then
update t_audit set insert_count = insert_count + 1 where names = 'EMP';
when updating then
update t_audit set update_count = update_count + 1 where names = 'EMP';
when deleting then
update t_audit set delete_count = delete_count + 1 where names = 'EMP';
end case;
end;
2.行触发器
行触发器是指执行DML操做时，每做用一行就触发一次的触发器。审计数据变化时，可使用行触发器。
语法：
create or replace trigger trigger_name
timing event1 [or event2 or event3]
on table_name
[reference OLD AS old|NEW AS new]
FOR EACH ROW
[when condition]
PL/SQL block;
reference:用于指定新，旧数据的方式。默认状况下OLD修饰符引用旧数据，使用NEW修饰符引用新数据。
FOR EACH ROW：表示创建行触发器
(1)before行触发器
//确保雇员工资不低于其原有工资
create or replace trigger tr_update_sal before update of sal on emp for each row begin if :NEW.sal < :OLD.sal then raise_application_error(-20001,'salary up'); end if; end;
(2)
//审计雇员工资的变化
--首先建立用于统计的表
CREATE TABLE "T_AUDIT_SALARY"
("ENAMES" VARCHAR2(50),
"OLDSAL" NUMBER(7,2),
"NEWSAL" NUMBER(7,2),
"CREATE_DATE" TIMESTAMP
);
--建立触发器，after后跟触发事件
create or replace trigger tr_audit_salary
after update of sal on emp
for each row
--after update of sal on emp指定触发的字段(sal),以及对此字段的操做(update)
declare
v_tmp number;
begin
--if判断用于新增一条统计信息,:old.ename为emp表中雇员姓名，当首次更新emp表中某一位雇员姓名时，把此雇员姓名(:old.ename)做为一行新增到统计表中
select count(*) into v_tmp from t_audit_salary where enames=:old.ename;
if v_tmp =0 then
insert into t_audit_salary values(:old.ename,:old.sal,:new.sal,sysdate);
else
update t_audit_salary set oldsal=:old.sal,newsal=:new.sal,create_date =sysdate
where enames=:old.ename;
end if;
end;
3.系统事件触发器
系统事件触发器指基于oracle系统事件所创建的触发器，提供了跟踪系统内或数据库变化的机制。
常见的系统事件属性函数：
ora_client_ip_address:用于返回客户端的IP地址。
ora_database_name:用于返回当前数据库名。
ora_login_user:用于返回登陆用户名。
ora_sysevent：用于返回触发触发器的系统事件名。
//记录用户登陆和退出
首先建立记录表
create table t_login(username varchar2(50),logon_date date,logoff_date date,address varchar2(15));
建立登录数据库事件触发器
create or replace trigger tr_login
after logon on database
begin
insert into t_login(username,logon_date,address)
values(ora_login_user,sysdate,ora_client_ip_address);
end;
建立退出数据库触发器
create or replace trigger tr_logoff
before logoff on database
--before操做说明是在退出数据库以前触发
begin
insert into t_login(username,logoff_date)
values(ora_login_user,sysdate);
end;
//记录数据库启动和关闭
--首先建立记录表
create table t_sysevent(events varchar2(50),create_date timestamp);
--数据库启动触发器
create or replace trigger tr_startup
after startup on database
begin
insert into t_sysevent values(ora_sysevent,sysdate);
end;
--数据库关闭触发器
create or replace trigger tr_shutdown
before shutdown on database
begin
insert into t_sysevent values(ora_sysevent,sysdate);
end;
四.管理触发器
--查看做用与某张表上的全部触发器
select TRIGGER_NAME,table_name,status from user_triggers where table_name='EMP';
---禁止某个触发器
alter trigger trigger_name disable;
---启用触发器
alter trigger TR_STARTUP enable;