语句触发器:
所谓语句触发器就是当执行insert,update,delete的前后(before,after)执行的各种操作。
行触发器:
实现表person主键值id递增
instead of触发器
用户事件触发器;
实现当用户删除所拥有的对象时,将对象的名称和类型以及删除时间写入日志记录表dropped_objects
所谓语句触发器就是当执行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