oracle触发器

各种触发器的栗子：
首先创建部门日志表
create table dept_log(operate_tag varchar2(10),operate_time date)

创建触发器
create or replace trigger tri_dept
    before insert or update or delete
    on dept
declare
    var_tag varchar2(10);
begin
    if inserting then
        var_tag:='插入了数据'；
    elsif updating then
        var_tag:='修改了数据'；
    elsif deleting then
        var_tag:='删除'；
    end if;
    insert into dept_log values (var_tag,sysdate);
end tri_dept;
/

条件谓词：
if updating(name) then 
    do something about update name;
end if;

如果修改了表的某列则触发。

行级触发器，执行DML操作所影响的每一行数据都会执行一次触发器
创建一个商品存储表
create table goods(
    id int primary key,
    good_name varchar2(50)
);

create sequence seq_id;

创建行级触发器
create or replace trigger tri_insert_good
    before insert
    on goods
    for each row
begin
select seq_id.nextval into :new.id from dual;
end;
/

这里使用:new.id关键字--列标识符、
这个标识符用来指向新行的id列，给它赋值，就相当于给当前行的id赋值
原值标识符，记作：:old.column 通常在update和delete语句中使用
新值标识符。记作：:new.column 通常在insert和update语句中使用

替换触发器
替换触发器定义在视图，由于视图是由多个基表连接组成的逻辑结构，所以一般不允许用户进行DML操作
当用户为视图编写替换触发器后，用户对视图的DML操作实际上就变成了执行触发器中的PL/SQL语句块，
这样就可以通过在“替换触发器”中编写适当的代码对构成视图的各个基表进行操作。

首先创建一个视图
授权创建视图
grant create view to scott;
创建视图
create view view_emp_dept as 
select empno,ename,dept,depno,dname,job,hiredate from emp,dept where emp.deptno = dept.deptno

在视图上创建行级替换触发器
create or replace trigger tri_insert_view
    insead of insert
    on view_emp_dept
    for each row
declare
    row_dept dept%rowtype;
begin
    select * into row_dept from dept where deptno =: new.deptno;//插入后的新行的id值
    //先判断dept表中是否有值，没有则插入数据，这样关联的表不会报错。
    if sql%notfound then
        insert into dept(deptno,dname) values (:new.deptno,:new.dname);
    end if;
    insert into emp(emono,ename,deptno,job,hiredate) values (:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
    end tri_insert_view;
    /

    用户事件触发器
    引起常见的触发器类型有：
    create alter drop analyze comment,grant revoke rename truncate suspend logon logoff

创建一个日志表
create table ddl_oper_log(
    db_obj_name varchar2(20),
    db_obj_type varchar2(20),
    oper_action varchar2(20),
    oper_user varchar2(20),
    oper_date date
)
创建用户事件触发器
create or replace trigger tri_ddl_oper
    before create or alter or drop
    on scott.schema //在模式下的操作都会引发该触发器
begin 
    insert into ddl_oper_log values(
        ora_dict_obj_name, //操作的数据对象名称
        ora_dict_obj_type, //操作的数据对象类型
        ora_sysevent, //系统事件名称
        sysdate //登录用户
    );
end;
/

程序包
程序包的规范
程序包一定要在“包主体”之前被创建

//声明规范
create or replace package pack_emp is
function fun_avg_sal(num_deptno number) return number;
procedure pro_regulate_sal(var_obj varchar2,num_proportion number);
end pack_emp;
/
具体实现的程序包
create or replace package body pack_emp is
        function fun_avg_sal(num_deptno number) return number is num_avg_sal number;
    begin
        select avg(sal) into num_avg_sal from emp where deptno = num_deptno;
        return(num_avg_sal);
        exception
            when no_data_found then
                dbms_output.put_line('该部门编号不存在')
            return 0；
        end fun_avg_sal;

    procedure pro_regulate_sal(var_obj varchar2,num_proportion number) is
    begin
        update emp
        set sal = sal*(1+num_proportion) where job = var_obj;
    end pro_regulate_sal;
end pack_emp;
/
调用
declare
    num_deptno emp.deptno%type;
    num_avg_sal emp.sal%type;

    var_obj emp%type;
    num_proportion number;
begin
    num_deptno:=10;
    num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);
    dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal)

    var_obj:='salesman';
    num_proportion:=0.1;
    package_emp.pro_regulate_sal(var_job,num_proportion);
end;
/