14.txt

Oracle database 10g基础教程（第二版） 源码

create trigger biufer_emp_empno
before insert or update
of empno
on emp
referencing old as old_value
            new as new_value
for each row 
when(new_value.empno<>7782)
begin
  :new_value.comm := 0;
end;
/

 



create table foo(a number);
create trigger biud_foo_a
before insert or update or delete
on foo
begin
  if user not in ('Clinton', 'Hillary') then 
    raise_application_error(-20001, '你没有修改此表的权限.');
  end if;
end;
/




create trigger bi_fo
before insert
on foo
begin
  null
end;
/



show errors


create table emp_copy
as
select empno, ename, job, sal
from emp;


create table emp_copy_log(
who varchar2(30),
when date)
/


create trigger biufer_emp_copy
before insert or update or delete
on emp_copy
begin
  insert into emp_copy_log values(user, sysdate);
end;
/

update emp_copy
set sal = sal * 2.0;
select *
from emp_copy_log;


create table person(
personID number primary key,
personName varchar2(20)
);
create sequence person_seq;



create trigger bifer_person_personID_pk
before insert
on person
for each row
begin
  select person_seq.nextval
  into :new.personID
  from dual;
end;
/



insert into person(personName) values('Athos');
insert into person(personID, personName) values(5678, 'Porthos');
insert into person(personName) values('Aramis');
select *
from person;



create or replace view emp_job
as
select empno, ename, job
from emp;



create trigger update_emp_job_view
instead of update
on emp_job
begin
  update emp
  set empno = :new.empno,
      ename = :new.ename,
      job = :new.job
  where empno = :old.empno;
end;
/



connect scott/tiger
create table dropped_objects(
object_name varchar2(30),
object_type varchar2(30),
dropped_on date);



create or replace
trigger log_drop_trigger
before drop
on scott.schema
begin
  insert into dropped_objects
  values(ora_dict_obj_name, ora_dict_obj_type, sysdate);
end;
/





create table drop_table(
col1 number,
col2 number
);
create view view_drop_table(columnA, columnB)
as
select col1, col2
from drop_table;
drop view view_drop_table;
drop table drop_table;





select *
from dropped_objects;



desc user_triggers


select trigger_name, table_name
from user_triggers;