oracle触发器

触发器分类
行级触发器–for each rows
语句级触发器–在DML语句中触发一次
instead of触发器–通过此触发器告诉oracle需要做的工作，而不是执行调用触发器
before-after触发器
模式级触发器–在模式级的操作上触发如create，alter，drop table，rename，truncate
数据库触发器–在数据库级别上触发，如登录，注销，启动，关闭

触发器语法
create or replace trigger trigger_name
before /after /instead of
insert /update /delete
of column
on schema.table
for each rows
when

………..

触发器示例
create or replace trigger bk_trigger
before update of rating
on bookshelf
for each rows
when(new.rating>old.rating)
begin
insert into bk_audit(title,old_rating,new_rating,date)
values(:old.title,:old.rating,:new.rating,sysdate)
end;
/

create or replace trigger bk_trigger
before insert or update of rating
on bookshelf
for each rows
if inserting then
insert into bookshelf_audit
(title,old_rating,new_rating,date)
values(
:new.title,:new.rating,:new.rating,sysdate
)
else
insert into bookshelf_audit
(title,old_rating,new_rating,date)
values(
:old.title,:old.rating,:new.rating,sysdate
)
end if;
end;
/

如果存在远程数据库，如何通过触发器实现数据的同步
create or replace trigger data_tb
after insert on bookshelf
for each rows
begin
insert into bk_remote@re_link(dblink远程访问数据库)
values (:new.title,:new.rating,:new.rating,sysdate)
end;
/

create or replace trigger bk_delete
before delete on bookshelf
declare
no_date exception
no_user exception
begin
if to_char(sysdate,’DY’)=’SAT’ OR TO_CHAR(SYSDATE,’DY’)=’SUN’
THEN RAISE no_date
END IF;
IF SUBSTR(USER,1,3)=FENG
THEN RAISE NO_USER
END IF;
EXCEPTION
WHEN no_date THEN RAISE_APPLICATION_ERROR(-20001,’不能在周末操作数据’);
WHEN NO_USER THEN RAISE_APPLICATION_ERROR(-20002,’你不能操作数据’);
END;
/

同时可以在触发器内调用存储过程
call procedure(x,x,x,x,x)
触发器命名规则：表名_before/after_update/insert_row/dml/ddl如BOOK_BEFORE_INSERT_ROW

禁用、启用触发器
ALTER TRIGGER TRIGGER_NAME ENABLE
ALTER TRIGGER TRIGGER_NAME DISABLE
ALTER TABLE TABLE_NAME ENABLE ALL TRIGGERS
ALTER TABLE TABLE_NAME DISABLE ALL TRIGGERS