oracle触发器

xiaoxiao2021-02-28  9

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

转载请注明原文地址: https://www.6miu.com/read-200062.html

最新回复(0)