触发器的创建与使用

触发器的创建与使用

 编写触发器执行代码注意事项：

 1）触发器代码的大小不能超过32K。如果确实需要使用大量代码建立触发器，应该首先建立存储过程，然后在触发器中使用call语句调用存储过程。

2）触发器只能包含SELECT,INSERT,UPDATE,DELETE语句，而不能包含DDL语句（CREATE,ALTER,DROP）和事务控制性语句（COMMIT,ROLLBACK和SAVEPOINT）。

语句触发器：

1、建立before语句触发器

CREATE OR REPLACE TRIGGER tr_sec_emp
  BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
  IF to_char(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN
    raise_applicaton_error(-20001, '不能在休息日改变雇员信息');
  END IF;
END;

当有多个触发事件时，可以将其区分：
CREATE OR REPLACE TRIGGER tr_sec_emp
  BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
  IF to_char(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN
    CASE
      WHEN INSERTING THEN  --当触发事件是insert时
        raise_applicaton_error(-20001, '不能在休息日增加雇员');
      WHEN UPDATING THEN   --当触发事件是update时
        raise_applicaton_error(-20001, '不能在休息日修改雇员信息');
      WHEN DELETING THEN   --当触发事件是delete时
        raise_applicaton_error(-20001, '不能在休息日解雇雇员');
    END CASE; END IF;
END;

 2、建立after语句触发器

--创建表audit_table

CREATE TABLE audit_table(
name VARCHAR2(20),ins INT,upd INT,del INT,starttime DATE,endtime DATE);
--创建AFTER语句触发器
CREATE OR REPLACE TRIGGER tr_audit_emp
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
  v_temp INT;
BEGIN
  SELECT count(*) INTO v_temp FROM audit_table
    WHERE name='EMP';
  IF v_temp=0 THEN
    INSERT INTO audit_table VALUES
    ('EMP',0,0,0,SYSDATE,NULL);
  END IF;
  CASE
    WHEN INSERTING THEN
      UPDATE audit_table SET ins=ins+1,endtime=SYSDATE
        WHERE name='EMP';
    WHEN UPDATING THEN
      UPDATE audit_table SET upd=upd+1,endtime=SYSDATE
        WHERE name='EMP';
    WHEN DELETING THEN
      UPDATE audit_table SET del=del+1,endtime=SYSDATE
        WHERE name='EMP';
    END CASE;
  END;

--往emp表加一条数据

INSERT INTO EMP VALUES (8,'张三','manager',7839,to_date('1982-06-07','YYYY-mm-dd'),3450,null,10);

 --更新一条记录（由于执行两次，所以upd值为2）

UPDATE EMP SET ename='李四' WHERE empno=8;

--删除一条记录
DELETE FROM EMP WHERE empno=8;

行触发器：
--创建before行触发器
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal ON EMP
FOR EACH ROW
BEGIN
  IF :new.sal<:old.sal THEN
    raise_application_error(-2000,'工资只涨不降');
  END IF;
END;      
      
UPDATE EMP SET sal =1000 WHERE empno=7; --员工编号7的原来工资为2450，而更新为1000，触发器不允许

--创建after行触发器

CREATE TABLE audit_emp_change(
name VARCHAR2(10),oldsal NUMBER(6,2),newsal NUMBER(6,2),time DATE);

--创建该触发器用于记录工资发生变化的日期
CREATE OR REPLACE TRIGGER tr_sal_change
  AFTER UPDATE OF sal ON emp
  FOR EACH ROW
DECLARE
  v_temp INT;
BEGIN
  SELECT count(*) INTO v_temp FROM audit_emp_change WHERE name = :old.name;
  IF v_tmep = 0 THEN
    INSERT INTO audit_emp_change
    VALUES
      (:old.ename, :old.sal, :new.sal, SYSDATE);
  ELSE
    UPDATE audit_emp_change
       SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE
     WHERE name = :old.ename;
  END IF;
END;

--限制行触发器
CREATE OR REPLACE TRIGGER tr_sal_change
  AFTER UPDATE OF sal ON EMP
  FOR EACH ROW
  WHEN (old.job = 'SALESMAN')
DECLARE
  v_temp INT;
BEGIN
  SELECT count(*)
    INTO v_temp
    FROM audit_emp_change
   WHERE name = :old.ename;
  IF v_temp = 0 THEN
    INSERT INTO audit_emp_change
    VALUES
      (:old.ename, :old.sal, :new.sal, SYSDATE);
  ELSE
    UPDATE audit_emp_change
       SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE
     WHERE name = :old.ename;
  END IF;
END;

 

DML语句触发器注意事项：

 触发器代码不能触发器所对应的基表中读取数据。如要基于EMP表建立触发器，那么该触发器的执行代码不能包含对EMP表的查询操作。尽管在建立触发器时不会出现任何错误，但在执行相应触发操作时会显示错误信息。假设希望雇员工资不能超过当前的最高工资，并使用触发器实现该规则：

CREATE OR REPLACE TRIGGER tr_emp_sal
  BEFORE UPDATE OF sal ON emp
  FOR EACH ROW
DECLARE
  maxsal NUMBER(6, 2);
BEGIN
  SELECT max(sal) INTO maxsal FROM emp;
  IF :new.sal > maxsal THEN
    raise_application_error(-20001, '超出工资上限');
  END IF;
END;
 
 UPDATE emp SET sal=6000 WHERE empno=7788;

执行上述update操作时提示错误如下图所示：

 

 说明：为满足数据库数据特定规则，可以使用约束、触发器、子程序实现。因为约束性能最好，实现最简单，所以首选约束；如果约束不能实现特定规则，那么应该选择触发器；如果触发器仍然不能满足实现特定规则，那么应该选择子程序（过程和函数）。

1）如为了实现雇员工资不能低于800元，可以选用check约束，示例如下：

alter table emp add constraint ck_sal check （sal>=800）;

2）如假定雇员工资不能低于其原工资，但也不能高于原工资的20%，使用约束无法实现，但可以通过触发器实现。示例如下：

create or replace trigger tr_check_sal

before update of sal on emp

for each row

when （new.sal<old.sal or new.sal>1.2*old.sal）

begin

 raise_application_error(-20931,'工资只升不降，并且升幅不能超过20%');

end;

INSTEAD OF触发器
    可以在表或视图上指定INSTEAD OF触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE)，每一个表或视图只能有一个INSTEAD OF触发器。
　　 不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器。
    INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用。INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

当视图中包含下面的结构之一时，就是不可更新的视图：

●  集合运算符（union、union all、intersect、minus）

●  分组函数（min、max、sum、avg、count等）

●  CASE或DECODE语句

●  CONNECT BY、GROUP BY、HAVING或START WITH子句

●  DISTINCT运算符

●  连接(当包含连接键时会引发异常)

    建立INSTEAD OF 触发器注意事项：
●INSTEAD OF选项只适用于视图；
●当基于视图建立触发器时，不能指定before和after选项；
●在建立视图时没有指定with check option选择；
●当建立INSTEAD OF触发器时，必须指定for each row选项

创建一个基于连接的视图
create or replace view dept_emp as
  select a.deptno,a.deptname,b.empno,b.ename from dept a,emp b where a.deptno=b.deptno;
可以直接查询该视图，但无法执行insert操作：insert into dept_emp values(50,'admin','1223','mary')

因此，创建一个instead of 触发器
create or replace trigger tr_instead_of_dept_emp
instead of insert on dept_emp
for each row
declare
 v_temp int;
begin
  select count(*) into v_temp from dept
    where deptno=:new.deptno;
  if v_temp=0 then
    insert into dept(deptno,deptname)
      values(:new.deptno,:new.deptname);
  end if;
  select count(*) into v_temp from emp
    where empno=:new.empno;
  if v_temp=0 then
    insert into emp (empno,ename,deptno)
      values(:new.empno,:new.ename,:new.deptno);
  end if;
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 返回触发器的系统事件名。
 
--创建例程启动和关闭的触发器，用于记录例程启动和关闭的事件和时间
 首先创建时间表event_table表
 create table event_table(event varchar2(30),time date);
 例程启动触发器只能使用after关键字，而例程关闭触发器只能使用before关键字
create or replace trigger tr_startup
after startup on database
begin
  insert into event_table values(ora_sysevent,sysdate);
end;
 
create or replace trigger tr_shutdown
before shutdown on database
begin
  insert into event_table values(ora_sysevent,sysdate);
end;
 
select * from event_table  
 
--建立登录和退出触发器
  用于记载用户登录和退出时间，可以分别建立登录和退出触发器。
  首先建立表log_table
create table log_table(username varchar2(20),logon_time date,logoff date,address varchar2(20))  
登录触发器只能使用after关键字，而退出触发器只能使用before关键字
create or replace trigger tr_logon
after logon on database
begin
  insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
 
create or replace trigger tr_logoff
before logoff on database;
begin
  insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;  

select * from log_table