oracle 触发器

DML 触发器： 
对表执行Insert、Update、Delete操作时激发 
可以用于执行校验、设置初使值、审核改变、甚至禁止某种DML操作 
语法： 
CREATE OR REPLACE TRIGGER 触发器名称 
{AFTER|BEFORE } -- 指定触发时机 
{INSERT OR DELETE OR UPDATE} -- 指定触发器事件 
ON 表名  --指定所监控的表 
{FOR EACH ROW|FOR EACH STATEMENT} -- 指定触发器次数 
BEGIN 
--代码; 
END; 
  
相关概念： 
AFTER|BEFORE：在什么事件之前或之后执行 
INSERT|DELETE|UPDATE：什么事件 
ON 表名：触发器建在什么表上，即监控什么表 
FOR EACH ROW：行级触发，示例：delete from t1,删除1000行，则执行1000次(一行一次) 
FOR EACH STATEMENT：语句级触发，示例： delete from t1,删除1000行，则执行1次(一句一次) 
:new 行变量：保存事件发生时新数据所在行，只有insert事件和update事件才有新数据 
:old 行变量：保存事件发生时旧数据所在行，只有delete事件和update事件才有旧数据 
  
示例：指出事件，及事件中的新数据和旧数据 
insert into emp(empno,ename) values(51,'job'); 
分析：只有一行新数据(51,job)，对应:new变量。 

update emp set ename='oracle' where empno = 51; 
分析：旧数据 (51,job)   ，对应:old变量。 
新数据(51,oracle)，对应:new变量。 

delete from emp where empno = 51; 
分析：只有一行旧数据(51,oracle),对应:old变量。 
Sql代码 
-- 显示触发器   
select trigger_name,status from user_triggers;   
-- 禁止触发器   
alter trigger tr_emp_salary disable;   
-- 激活触发器   
alter trigger tr_emp_salary enable;   
-- 禁止表的所有触发器   
alter table employee disable all triggers;   
-- 激活表的所有触发器   
alter table employee enable all triggers;   
-- 重新编译触发器   
--alter table tr_emp_salary compile;   
-- 删除触发器   
drop trigger tr_emp_salary;  

-- 显示触发器
select trigger_name,status from user_triggers;
-- 禁止触发器
alter trigger tr_emp_salary disable;
-- 激活触发器
alter trigger tr_emp_salary enable;
-- 禁止表的所有触发器
alter table employee disable all triggers;
-- 激活表的所有触发器
alter table employee enable all triggers;
-- 重新编译触发器
--alter table tr_emp_salary compile;
-- 删除触发器
drop trigger tr_emp_salary;

Sql代码 
-- 语句级触发器   
-- 禁止员工在休息日改变雇员信息   
create or replace trigger tr_sec_emp   
  -- before:在DML事件之前执行   
  before insert or update or delete  
  on employee   
  -- 如果在这里没有指定是行级还是语句级触发器，默认就是 FOR EACH STATEMENT   
begin  
  if to_char(sysdate,'DAY','nls_date_language=AMERICAN')   
     in('SAT','SUN') then  
        case  
           -- 当触发事件是Insert操作时，该条件谓词返回值为True，否则为False   
           when inserting then  
              raise_application_error(-20001,'不能在休息日增加雇员信息！');   
           -- 当触发事件是Update操作时，该条件谓词返回值为True，否则为False   
           when updating then  
              raise_application_error(-20002,'不能在休息日修改雇员信息！');   
           -- 当触发事件是Delete操作时，该条件谓词返回值为True，否则为False   
           when deleting then  
              raise_application_error(-20003,'不能在休息日删除雇员信息！');   
        end case;   
  end if;      
end;   
/  

-- 语句级触发器
-- 禁止员工在休息日改变雇员信息
create or replace trigger tr_sec_emp
  -- before:在DML事件之前执行
  before insert or update or delete
  on employee
  -- 如果在这里没有指定是行级还是语句级触发器，默认就是 FOR EACH STATEMENT
begin
  if to_char(sysdate,'DAY','nls_date_language=AMERICAN')
     in('SAT','SUN') then
        case
           -- 当触发事件是Insert操作时，该条件谓词返回值为True，否则为False
           when inserting then
              raise_application_error(-20001,'不能在休息日增加雇员信息！');
           -- 当触发事件是Update操作时，该条件谓词返回值为True，否则为False
           when updating then
              raise_application_error(-20002,'不能在休息日修改雇员信息！');
           -- 当触发事件是Delete操作时，该条件谓词返回值为True，否则为False
           when deleting then
              raise_application_error(-20003,'不能在休息日删除雇员信息！');
        end case;
  end if;   
end;
/

Sql代码 
-- 限制员工的工资不能超过当前的最高工资   
create or replace trigger tr_emp_salary before   
  -- update of 后面指定在那些列被改变时才调用这个触器，如果不加of 默认是所有列   
  update of salary on employee   
  -- 指定该触发器为行级触发器   
  for each row   
-- 如果要定义变量，则只能在Declare中定义   
declare  
  maxSalary number(10,2);   
begin  
  select max(salary) into maxSalary from employee;   
  if :new.salary > maxSalary then  
     raise_application_error(-20010,'员工工资超出工资上限！');   
  end if;   
end;   
/  

-- 限制员工的工资不能超过当前的最高工资
create or replace trigger tr_emp_salary before
  -- update of 后面指定在那些列被改变时才调用这个触器，如果不加of 默认是所有列
  update of salary on employee
  -- 指定该触发器为行级触发器
  for each row
-- 如果要定义变量，则只能在Declare中定义
declare
  maxSalary number(10,2);
begin
  select max(salary) into maxSalary from employee;
  if :new.salary > maxSalary then
     raise_application_error(-20010,'员工工资超出工资上限！');
  end if;
end;
/

Sql代码 
-- 设置员工的工资不能低于原工资，但也不能高出原工资的20%   
create or replace trigger tr_emp_say before update of salary   
  on employee for each row   
  -- 设置执行触发器的条件   
  when (new.salary < old.salary or new.salary > old.salary*1.2)   
  begin  
    raise_application_error(-20011,'员工的不能降薪，但工资升幅不能超过20%！');    
end;   

-- 设置员工的工资不能低于原工资，但也不能高出原工资的20%
create or replace trigger tr_emp_say before update of salary
  on employee for each row
  -- 设置执行触发器的条件
  when (new.salary < old.salary or new.salary > old.salary*1.2)
  begin
    raise_application_error(-20011,'员工的不能降薪，但工资升幅不能超过20%！'); 
end; 

在CMD中执行：audit insert,update,delete on employee by access; 
可以设置Employee表的审计选项，如果在Employee表上执行了Insert、 
Update和Delete操作，Oracle会将关于SQL操作的信息(用户、时间等) 
写入数据字典中，但使用数据库审计只能审计SQL操作，而不能记载数据变化 

Instead of 触发器： 
它是DML触发器的替代品，控制对视图的操作，它可以使不能更新的视图变为可更新， 
以及覆盖可更新的视图的行为  
注意： 
Instead of 选项只适用于视图 
当基于视图建立触发器时，不能指定Before和After选项 
在建立视图时没有指定with check option选项 
当建立Instead of触发器时，必须指定for each row选项 
Sql代码 
-- 创建用户表   
-- drop table users;   
create table users(   
       userId int not null,   
       username varchar(20) not null,   
       password varchar2(50) not null,   
       logintime date not null  
)   
-- 向用户表中插入数据   
insert into users values(1,'user1',11111,sysdate);   
insert into users values(2,'user2',22222,sysdate);   
insert into users values(3,'user3',33333,sysdate);   
insert into users values(4,'user4',44444,sysdate);   
insert into users values(5,'user5',55555,sysdate);   
select * from users;   
-- 建立复杂视图   
-- drop view emp_users;   
create or replace view emp_users as  
select distinct e.empno,e.name,u.username from employee e,users u where e.empno = u.userId;   
  
select * from emp_users;   
-- drop view tr_instead_of_emp_users;   
create or replace trigger tr_instead_of_emp_users   
instead of insert on emp_users for each row   
declare  
   i_temp int;   
begin  
   -- 同时向两张表中插入数据   
   select count(*) into i_temp from employee where empno = :new.empno;   
   if i_temp = 0 then  
      insert into Employee values(:new.empno,:new.name,'销售经理',1,'2009-01-09',5300,'销售',4);   
   end if;   
      
   select count(*) into i_temp from users where userId = :new.empno;   
   if  i_temp = 0 then  
      insert into users values(:new.empno,:new.username,55555,sysdate);   
   end if;   
end;   
/  

-- 创建用户表
-- drop table users;
create table users(
       userId int not null,
       username varchar(20) not null,
       password varchar2(50) not null,
       logintime date not null
)
-- 向用户表中插入数据
insert into users values(1,'user1',11111,sysdate);
insert into users values(2,'user2',22222,sysdate);
insert into users values(3,'user3',33333,sysdate);
insert into users values(4,'user4',44444,sysdate);
insert into users values(5,'user5',55555,sysdate);
select * from users;
-- 建立复杂视图
-- drop view emp_users;
create or replace view emp_users as
select distinct e.empno,e.name,u.username from employee e,users u where e.empno = u.userId;

select * from emp_users;
-- drop view tr_instead_of_emp_users;
create or replace trigger tr_instead_of_emp_users
instead of insert on emp_users for each row
declare
   i_temp int;
begin
   -- 同时向两张表中插入数据
   select count(*) into i_temp from employee where empno = :new.empno;
   if i_temp = 0 then
      insert into Employee values(:new.empno,:new.name,'销售经理',1,'2009-01-09',5300,'销售',4);
   end if;
   
   select count(*) into i_temp from users where userId = :new.empno;
   if  i_temp = 0 then
      insert into users values(:new.empno,:new.username,55555,sysdate);
   end if;
end;
/

Sql代码 
-- 测试Instead of 触发器   
insert into emp_users values(10,'王五','小李');   
insert into emp_users values(11,'张三','小明');   
select * from emp_users;  

-- 测试Instead of 触发器
insert into emp_users values(10,'王五','小李');
insert into emp_users values(11,'张三','小明');
select * from emp_users;

数据库事件触发器： 
在数据库启动、关闭、用户登录、退出或者Oracle错误发生时，以及执行创建、删除表、索引等DDL语句时激发 
主要用于跟踪数据库活动 
创建打开数据库触发器：after startup 
创建用户登录触发器：after logon 
创建用户退出触发器：before logoff 
创建DDL触发器：after ddl 
Sql代码 
-- 建立用户登录的触发器   
create or replace trigger tr_logon after logon on database  
   begin  
   raise_application_error(-20001,ora_login_user || '用户登录数据库！');   
end;   
/   
-- 建立用户退出的触发器   
create or replace trigger tr_logoff before logoff on database  
   begin  
   raise_application_error(-20002,ora_login_user || '用户退出数据库！');   
end;   
/   

-- 创建DDL日志表   
create table ddl_log   
(   
  -- DDL事件   
  ddl_event varchar2(20),   
  -- 对应的数据库登录用户名   
  username varchar2(10),   
  -- 对应的对象的所有者名   
  owner varchar2(10),   
  -- 对应的数据库对象名   
  objname varchar2(50),   
  -- 对应的数据库对象类型   
  objtype varchar2(10),   
  -- 对应的数据库操作时间   
  time date  
)   
-- 创建DDL触发器   
create or replace trigger tr_ddl_log   
after ddl on database  
begin  
      insert into ddl_log values(   
      ora_sysevent,ora_login_user,ora_dict_obj_owner,   
      ora_dict_obj_name,ora_dict_obj_type,sysdate   
      );   
end;   
  
select * from ddl_log   
-- 测试DDL触发器   
create table aa(aid int)   
drop table aa