Oracle系列之触发器

涉及到表的处理请参看原表结构与数据  Oracle建表插数据等等

创建一个触发器，使其可以修改tb_Department表的deptno。

create or replace trigger update_dept
after update on deptno
for each row
begin
update tb_Department set deptno=:new.deptno where deptno=:old.deptno;
end;
/

建立一个语句级触发器，防止在非工作时间对于某个表的更新，插入和删除

create or replace trigger tr_dept_time
before insert or delete or update
on tb_Department
begin
if(to_char(sysdate, 'DAY') in ('星期六', '星期日')) or
 (to_char(sysdate, 'HH24:MI') not between '08:00' and '18:00')
then
raise_application_error(-20001, '非工作时间，不能对tb_Department修改');
end if;
end;
/

select trigger_name, trigger_type from user_triggers
where table_name = 'tb_Department';

在tb_Department表中加入一个列，用于统计部门的总工资，在tb_Employee表上建立一个行级触发器，用于同步这个数据

alter table tb_Department add  sum_salary number(10) default 0;
declare
sum_sal tb_Employee.salary % type;
cursor dep_sal_cur is
select deptno,sum(sal) as sum_sal
from tb_Employee
group by deptno;
begin
for dep_sal in dep_sal_cur loop
update tb_Department 
set sum_salary = dep_sal.sum_sal
where deptno = dep_sal.deptno;
end loop; 
end;
create or replace trigger tri_emp_dep
after insert or delete or update of sal
on tb_Employee
for each row
begin
dbms_output.put_line('行触发器执行');
update departments 
set sum_salary = sum_salary + nvl(:new.salary, 0) - nvl(:old.salary, 0);
end;
/

使用序列和触发器，给流水号列自动添加流水号。

create sequence seq_table1 
start with 1
increment by 1
maxvalue 9999999;
create table table1(num1 number, name1 varchar2(20));
create or replace trigger trg_table1
before insert on table1
for each row
declare
begin
select seq_table1.nextval into :new.num1 from dual;
end;
/

在一个多事件的触发器中，使用条件谓词，对插入，删除，更新进行不同的处理。

create or replace trigger mul_event_tri
after insert or delete or update on table1
begin
case 
when inserting then
dbms_output.put_line('data has been inserted');
when deleting then
dbms_output.put_line('data has been deleted');
when updating then
dbms_output.put_line('data has been updated');
end case;
end;
/

建立一个审计表，把某个表更新，插入，删除的数据，通过行级触发器，记录到审计表，需要记录更新前后数据的变化，操作的类型和时间。

create table audit_table1(  
old_num number,
new_num number,
old_name varchar2(20),
new_name varchar2(20),
op_type varchar2(20),
op_date varchar2(20)
);

create or replace trigger audit_tri
before update or insert or delete
on table1
for each row
declare 
str varchar2(20);
begin
case 
when updating then
str := 'update';
when inserting then
str := 'insert';
when deleting then
str := 'delete';
end case;
insert into audit_table1 values(
:old.num1, :new.num1, :old.name1, :new.name1, str, 
to_char(sysdate) || ' ' || to_char(sysdate, 'HH24:MI'));
end;
/
-- test -- 
insert into table1 values(17, 'TFJ');
select * from table1;
select * from audit_table1;
-- test --