触发器分类 行级触发器–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
