oracle数据库：触发器

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

create or replace trigger test
    before update or insert or delete
    on employees
begin
    if (to_char(sysdate,'DY') in ('星期六','星期日'))
        or (to_char(sysdate,'HH24:MI') not between '09:00' and '12:00')
        or (to_char(sysdate,'HH24:MI') not between '14:00' 
and '17:00')
    then
        raise_application_error(-20500,'You may insert into employees table only during business hours.');
    end if;
End test;

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

alter table Departments add (sum_salary1 number(10) default 0);
select * from Departments;

declare
  sum_sal Employees.salary%type;
  cursor dep_sal_cur is
    select department_id, sum(salary) as sum_sal
      from Employees
     group by department_id;
begin
  for dep_sal in dep_sal_cur loop
    update Departments
       set sum_salary1 = dep_sal.sum_sal
     where department_id = dep_sal.department_id;
  end loop;
end;

create or replace trigger tri_emp_dep
  after insert or delete or update of salary, department_id on Employees
  for each row
begin
  dbms_output.put_line('行触发器执行');
  update departments
     set sum_salary1 = sum_salary1 + nvl(:new.salary, 0) -
                       nvl(:old.salary, 0)
   where departments.department_id = :old.department_id;
end;

3、尝试在update事件的行级触发器中，对更新的表进行查询，这样会出现什么问题？

create or replace trigger test1
    before update
    on employees
    for each row
begin 
    for item in (select *
                    from employees
                    where employee_id<110)
    loop
        dbms_output.put_line(item.first_name||' '||item.last_name);
    end loop;
end;
update employees
    set first_name='ziheng'
    where employee_id=100;

4、尝试在触发器中使用事务管理，这样会出现什么问题？

由于触发器本身就是一种事务，在事务里面使用事务就会引发一些错误，如同两个事务不能同时操作一张表一样。触发器是按照 before触发器 行操作 after 触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行操作，那么会整个作为一个事务被回滚 ，但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚 这也是使用触发器要注意的地方之一。

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

update employees
    set first_name='ziheng'
    where employee_id=100;
    
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
begin
    select seq_table1.nextval into :new.num1 from dual;
end;

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

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;

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

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,'yyyy-mm-dd hh24:mi:ss'));
end;

insert into table1 values(17, 'TFJ');
select * from table1;
select * from audit_table1;