How to enable the trigger in Oracle
|
1 |
ALTER TRIGGER < trigger name > ENABLE; |
How to disable the trigger in Oracle
|
1 |
ALTER TRIGGER < trigger name > DISABLE; |
How to check Trigger status in Oracle
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
column OWNER format a10; column TABLE_NAME format a15; column STATUS format a10; column TRIGGER_NAME format a30; select OWNER,table_name, trigger_name,status FROM dba_triggers WHERE trigger_name = upper ('&trigger_name'); Introduzir valor para trigger_name: TRG_INC_UDP_TGFFIN_TRAVA antigo 2: dba_triggers WHERE trigger_name = upper ('&trigger_name') novo 2: dba_triggers WHERE trigger_name = upper ('TRG_INC_UDP_TGFFIN_TRAVA') OWNER TABLE_NAME TRIGGER_NAME STATUS ---------- --------------- ------------------------------ ---------- SANKHYA TGFCAB TRG_INC_UDP_TGFFIN_TRAVA ENABLED TESTE TGFCAB TRG_INC_UDP_TGFFIN_TRAVA DISABLED |
How to Check Triggers TABLE_NAME
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
column OWNER format a10; column TABLE_NAME format a15; column STATUS format a10; column TRIGGER_NAME format a30; select OWNER,table_name, trigger_name,status FROM dba_triggers WHERE table_name = upper ('&table_name'); OWNER TABLE_NAME TRIGGER_NAME STATUS ---------- --------------- ------------------------------ ---------- TREINA TGFCAB TRG_INC_UPD_TGFCAB_RASTST ENABLED TREINA TGFCAB TRG_INC_UPT_DLT_TGFCAB_INDENIZ ENABLED TREINA TGFCAB TRG_DLT_TGFCAB_AUDIT_CONF ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_GRANDES_CARGAS ENABLED TREINA TGFCAB TRG_INC_TGFCAB ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_TGAMOV ENABLED TREINA TGFCAB TRG_DLT_TGFCAB_ESTTERC ENABLED TREINA TGFCAB TRG_DLT_TGFCAB_AFTER ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_FLEX ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_AFTER ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_TRANSG ENABLED TREINA TGFCAB TRG_INC_UPD_TGFCAB_TGFGXE ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_SERIE ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_EST ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_TGFCPP ENABLED TREINA TGFCAB TRG_DLT_TGFCAB_METAS ENABLED TREINA TGFCAB TRG_INC_UPT_DLT_TGFCAB_FEC_CTB ENABLED TREINA TGFCAB TRG_UPD_TGFCAB_TCIBEM ENABLED |
How to recreate TRIGGER DDL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
set pagesize 0 set long 10000 SQL > SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRG_INC_UDP_TGFFIN_TRAVA','TESTE') FROM dual; CREATE OR REPLACE EDITIONABLE TRIGGER "TESTE"."TRG_INC_UDP_TGFFIN_TRAVA" BEFORE UPDATE ON SANKHYA.TGFCAB FOR EACH ROW DECLARE VC_COUNT INT; BEGIN IF (UPDATING('STATUSNOTA')) THEN SELECT COUNT(*) INTO VC_COUNT FROM TGFFIN WHERE NUNOTA = :NEW.NUNOTA AND CODTIPTIT = 0; IF VC_COUNT > 0 THEN RAISE_APPLICATION_ERROR(-20101,Ad_Fc_Formatahtml2('Nao e possivel confirmar a Nota/Pedido', 'Tipo de titulo 0 no financeiro.', 'Alterar o tipo de titulo no financeiro')); END IF; END IF; END; ALTER TRIGGER "TESTE"."TRG_INC_UDP_TGFFIN_TRAVA" DISABLE |
How to enable all the trigger on a table
|
1 |
ALTER TABLE <table name> enable ALL TRIGGERS; |
How to list all disabled triggers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
select table_name, trigger_name,status FROM user_triggers WHERE status='DISABLED'; column OWNER format a10; column TABLE_NAME format a15; column STATUS format a10; column TRIGGER_NAME format a30; SQL> select OWNER, table_name, trigger_name,status FROM dba_triggers WHERE status='DISABLED'; OWNER TABLE_NAME TRIGGER_NAME STATUS ---------- --------------- ------------------------------ ---------- SANKHYA TGFITE AD_TRG_TGFITE_LOTE_NFE_FORNEC DISABLED TESTE TGFITE AD_TRG_TGFITE_LOTE_NFE_FORNEC DISABLED TESTE TGFCAB TRG_INC_UDP_TGFFIN_TRAVA DISABLED SYS SYSLSBY_EDS_DDL_TRIG DISABLED SYS LOGMNRGGC_TRIGGER DISABLED WMSYS NO_VM_DDL DISABLED WMSYS NO_VM_DROP_A DISABLED MDSYS SDO_TOPO_DROP_FTBL DISABLED LBACSYS LBAC$AFTER_DROP DISABLED LBACSYS LBAC$BEFORE_ALTER DISABLED LBACSYS LBAC$AFTER_CREATE DISABLED |
How to List All Triggers in Tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
column OWNER format a10; column TABLE_NAME format a15; column STATUS format a10; column TRIGGER_NAME format a30; SQL> select OWNER, table_name, trigger_name,status FROM dba_triggers WHERE status='DISABLED'; OWNER TABLE_NAME TRIGGER_NAME STATUS ---------- --------------- ------------------------------ ---------- SANKHYA TGFITE AD_TRG_TGFITE_LOTE_NFE_FORNEC DISABLED TESTE TGFITE AD_TRG_TGFITE_LOTE_NFE_FORNEC DISABLED TESTE TGFCAB TRG_INC_UDP_TGFFIN_TRAVA DISABLED SYS SYSLSBY_EDS_DDL_TRIG DISABLED SYS LOGMNRGGC_TRIGGER DISABLED WMSYS NO_VM_DDL DISABLED WMSYS NO_VM_DROP_A DISABLED MDSYS SDO_TOPO_DROP_FTBL DISABLED LBACSYS LBAC$AFTER_DROP DISABLED LBACSYS LBAC$BEFORE_ALTER DISABLED LBACSYS LBAC$AFTER_CREATE DISABLED |