触发器练习

语句触发器：

所谓语句触发器就是当执行insert，update，delete的前后（before，after）执行的各种操作。

create table tt(
id number(2)
)

create table tt_log(
who varchar(16),
when date
)

create or replace trigger tt_tri
after insert or update or delete
on tt
begin
    insert into tt_log values(user,sysdate);
    end;
    /

insert into tt values(5)

select * from tt_log

行触发器：
实现表person主键值id递增

create table person(
id number(2) primary key,
name varchar(16)
)

create sequence person_seq start with 1 

desc person_seq

create or replace trigger bifer_person_id_PK
before insert 
on person
for each row
begin
    select PERSON_SEQ.nextval
    into :new.id
    from dual;
 end;
 /

 desc dual

insert into person(name) values('aa')

insert into person(id,name) values(11,'bb')

insert into person(name) values('cc')

select * from person

instead of触发器

  create table person(
id number(2) primary key,
name varchar(16)
)
 create or replace view person_view
   as
   select * 
   from
   person

   create or replace trigger update_person_view
   instead of update
   on person_view
   begin
        update person
        set id = :new.id,
            name = :new.name
        where id = :old.id;
   end;
   /

 update person_view set name='postino1111111' where id=1

 select * from person

如果提示用户没有创建view的权限，换个具有dba权限的用户登录执行：grant create view to 用户名

用户事件触发器;
实现当用户删除所拥有的对象时，将对象的名称和类型以及删除时间写入日志记录表dropped_objects

 create table dropped_objects(
 object_name varchar(32),
 object_type varchar(32),
 dropped_time date
  )

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

 create table test2(
 id number(2)
 )

 create or replace view test2_view
 as
 select * 
 from test2

 drop view test2_view

 drop table test2

 select * from dropped_objects