oracle之触发器

oracle触发器

 

create table tri_tab(
id int not null,
oper_type varchar2(20),
oper_time date,
 cha_EMPNO                                                                                              NUMBER(4),
cha_ENAME                                                                                              VARCHAR2(10),
 cha_JOB                                                                                                VARCHAR2(9),
 cha_MGR                                                                                                NUMBER(4),
 cha_HIREDATE                                                                                           DATE,
 cha_SAL                                                                                                NUMBER,
 cha_COMM                                                                                               NUMBER(7,2),
 cha_DEPTNO                                                                                             NUMBER(2)
 );
create sequence id
minvalue 1
maxvalue 99999999999
start with 1
increment by 1;
create or replace trigger trr_emp
after insert or update or delete on emp
for each row
begin
case
when inserting then
insert into tri_tab(oper_type,oper_time,cha_empno,cha_ename,cha_job,cha_mgr,cha_hiredate,cha_sal,cha_comm,cha_deptno)
values
('insert',sysdate,:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);
when deleting then
insert into tri_tab(oper_type,oper_time,cha_empno,cha_ename,cha_job,cha_mgr,cha_hiredate,cha_sal,cha_comm,cha_deptno)
values
('delete',sysdate,:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
when updating then
insert into tri_tab(oper_type,oper_time,cha_empno,cha_ename,cha_job,cha_mgr,cha_hiredate,cha_sal,cha_comm,cha_deptno)
values
('update',sysdate,:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end  case;
end;
/

 

 

create or replace trigger tr_col_emp
before update of ename on emp
begin
dbms_output.put_line('##############not allowed to modify the columns###############');
raise_application_error(-20000,'please call your dba');
end;
/
SCOTT@orcl#create or replace trigger tr_col_emp
  2  before update of ename on emp
  3  begin
  4  dbms_output.put_line('##############not allowed to modify the columns###############');
  5  raise_application_error(-20000,'please call your dba');
  6  end;
  7  /

触发器已创建

SCOTT@orcl#update emp set ename='xiaohai' where empno=2000;
##############not allowed to modify the columns###############
update emp set ename='xiaohai' where empno=2000
       *
第 1 行出现错误:
ORA-20000: please call your dba
ORA-06512: 在 "SCOTT.TR_COL_EMP", line 3
ORA-04088: 触发器 'SCOTT.TR_COL_EMP' 执行过程中出错

SCOTT@orcl#
SCOTT@orcl#ALTER TRIGGER TR_COL_EMP DISABLE;

触发器已更改

SCOTT@orcl#update emp set ename='xiaohai' where empno=2000;

已更新 1 行。

SCOTT@orcl#ALTER TRIGGER TR_COL_EMP ENABLE;

触发器已更改

SCOTT@orcl#update emp set ename='xiaohai' where empno=2000;
##############not allowed to modify the columns###############
update emp set ename='xiaohai' where empno=2000
       *
第 1 行出现错误:
ORA-20000: please call your dba
ORA-06512: 在 "SCOTT.TR_COL_EMP", line 3
ORA-04088: 触发器 'SCOTT.TR_COL_EMP' 执行过程中出错

SCOTT@orcl#