Oracle触发器_trigger

59 阅读 0 评论 39 点赞

我是靠谱客的博主高挑大侠，最近开发中收集的这篇文章主要介绍Oracle触发器_trigger，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1. 查询表上的触发器

select trigger_name from all_triggers where table_name='表名';
例如: select trigger_name from all_triggers where table_name='MDM_WLZSJ_CODE';

2. 批量禁用/启用触发器

/*禁用触发器*/SQL> alter trigger 触发器名称 disable;
/*启用触发器*/SQL> alter trigger 触发器名称 enable;
/*批量禁用触发器*/
select 'alter trigger '||''''||trigger_name||''' disable;' from all_triggers where table_name='MDM_WLZSJ_CODE';  
/*批量启用触发器*/
select 'alter trigger '||''''||trigger_name||''' enable;' from all_triggers where table_name='MDM_WLZSJ_CODE';

3. 创建触发器

/*2.1行转列,行更新时,列也更新*/

--INSERT OR 不用加,以提高效率
CREATE OR REPLACE TRIGGER TRG_MDM_SBZSJ_CODE0514 after UPDATE ON MDM_SBZSJ_CODE
  FOR EACH ROW
BEGIN

    -- 设备状态编号 DESC37,A027
    if :NEW.DESC37<>:OLD.DESC37 or (:NEW.DESC37 is null and :OLD.DESC37 is not null) or (:OLD.DESC37 is null and :NEW.DESC37 is not null)THEN
       update MDM_SBZSJ_CODEVALUE v set v.PROPERTYVALUE=:NEW.DESC37
       where 1=1
       and v.CODEID=:NEW.CODEID
       AND v.PROPERTYCODE='A027';
    END IF;
    -- 设备状态 DESC9,A009
    if :NEW.DESC9<>:OLD.DESC9 or (:NEW.DESC9 is null and :OLD.DESC9 is not null) or (:OLD.DESC9 is null and :NEW.DESC9 is not null) THEN
       update MDM_SBZSJ_CODEVALUE v set v.PROPERTYVALUE=:NEW.DESC9
       where 1=1
       and v.CODEID=:NEW.CODEID
       AND v.PROPERTYCODE='A009';
    END IF;

END TRG_MDM_SBZSJ_CODE0514;