Oracle 触发器

查看当前用户下的触发器：
desc user_triggers;
select trigger_name,description from user_triggers;

准备数据：

赋予scott创建各种资源的权限。
grant resource to scott;

create table staff (id varchar2(2),name varchar2(10),sex varchar2(2),corpid varchar2(2));
insert into staff(id,name,sex,corpid) values('01','李成','男','01');
insert into staff(id,name,sex,corpid) values('02','赵兰','女','05');
insert into staff(id,name,sex,corpid) values('03','刘丰','男','03');
insert into staff(id,name,sex,corpid) values('04','钱峰山','男','04');
insert into staff(id,name,sex,corpid) values('05','张凯','男','02');
insert into staff(id,name,sex,corpid) values('07','李想','男','04');
insert into staff(id,name,sex,corpid) values('06','刘丽','女','02');
insert into staff(id,name,sex,corpid) values('09','周恒久','男','04');
insert into staff(id,name,sex) values('08','孙小萍','女');

create table corp (id varchar2(2),name varchar2(10));
insert into corp(id,name) values('01','办公室');
insert into corp(id,name) values('02','财务科');
insert into corp(id,name) values('03','质量科');
insert into corp(id,name) values('04','生产部');
insert into corp(id,name) values('05','销售部');
insert into corp(id,name) values('06','后勤科');

DELETE行级触发器：
create or replace trigger del_corp
    after delete on corp for each row
begin
    delete from staff where corpid=:old.id; --old.id为corp的id。
end;
/

delete from corp where id='04';
select * from corp;
select * from staff;
rollback;
select * from corp;
select * from staff;
会看到删除corp编号为04的行，staff编号为04的corpid行也会全部删除。回滚后，又恢复了。

INSERT行级触发器：
create or replace trigger insert_corp
    after insert on corp for each row
begin
    insert into staff(id,name,sex,corpid) values('09','王大海','男',:new.id)
end;
/

insert into corp(id,name) values('07','三产办');

UPDATE行级触发器：
create or replace trigger update_corp
    after update on corp for each row
begin
    update staff set corpid=:new.id where corpid=:old.id;
end;
/

update corp set id='UN' where id='04';
select * from corp;
select * from staff;
rollback;

在触发器中不可以有commit/rollback;

触发器中定义异常：
create or replace trigger del_staff
    after delete on staff for each row
begin
    if :old.id='01' then
       raise_application_error(-20000,'禁止删除01行');
    end if;
end;
/
(自定义错误号必须在-20999~-20000之间)

语句级触发器：
create table emp2_log
(
    uname varchar2(20),
    action varchar2(10),
    atime date
);
create or replace trigger trig_emp2
    after insert or delete or update on emp2 for each row
begin
    if inserting then
        insert into emp2_log values (USER, 'insert', sysdate);
    elsif updating then
        insert into emp2_log values (USER, 'update', sysdate);
    elsif deleting then
        insert into emp2_log values (USER, 'delete', sysdate);
    end if;
end;
/

insert into emp2(empno,ename,job,sal,comm,deptno) values(1345,'LL','AA',1200,23,10);
select empno,ename,job,sal,comm,deptno from emp2;
select * from emp2_log;

create table auto (a number,b varchar2(10));
insert into auto values(myseq.nextval,'TESTING...');
create or replace trigger tr_inc_auto
    before insert on auto for each row
declare
    sn number(5);
begin
    select myseq.nextval into sn from dual;
    :new.a:=sn;
end;
/
insert into auto values(21,'inc');

替换触发器：
创建多表查询视图scview：
create or replace view scview(id,name,sex,corpid,company)
as
    select s.id,s.name,s.sex,c.id,c.name from staff s,corp c where s.corpid=c.id;
更新的替换触发器，只能创建在表上。

create or replace trigger tr_scview
    instead of insert on scview for each row
begin
    insert into corp values(:new.corpid,:new.corp);
    insert into staff values(:new.id,:new.name,:new.sex,:new.corpid);
end;
/
select * from scview;
insert into scview(id,name,sex,corpid,corp) values('09','小桃子','女','09','信息科');