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;