{"id":1199,"date":"2021-05-25T17:30:38","date_gmt":"2021-05-25T20:30:38","guid":{"rendered":"https:\/\/rodrigosilvaesilva.com.br\/?p=1199"},"modified":"2021-05-25T17:30:38","modified_gmt":"2021-05-25T20:30:38","slug":"triggers-enable-disable-recreate-ddl-status","status":"publish","type":"post","link":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/2021\/05\/25\/triggers-enable-disable-recreate-ddl-status\/","title":{"rendered":"Triggers enable\/disable\/recreate\/ddl\/status"},"content":{"rendered":"\n<p><strong>How to enable the trigger in Oracle<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >ALTER TRIGGER &lt; trigger name &gt; ENABLE;<\/pre><\/div>\n\n\n\n<p><strong>How to disable the trigger in Oracle<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >ALTER TRIGGER &lt; trigger name &gt; DISABLE;<\/pre><\/div>\n\n\n\n<p><strong>How to check Trigger status in Oracle<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >column OWNER format a10;\ncolumn TABLE_NAME format a15;\ncolumn STATUS format a10;\ncolumn TRIGGER_NAME format a30;\nselect OWNER,table_name, trigger_name,status FROM\ndba_triggers WHERE  trigger_name = upper ('&amp;trigger_name');\n\nIntroduzir valor para trigger_name: TRG_INC_UDP_TGFFIN_TRAVA\nantigo   2: dba_triggers WHERE  trigger_name = upper ('&amp;trigger_name')\nnovo   2: dba_triggers WHERE  trigger_name = upper ('TRG_INC_UDP_TGFFIN_TRAVA')\n\nOWNER      TABLE_NAME      TRIGGER_NAME                   STATUS\n---------- --------------- ------------------------------ ----------\nSANKHYA    TGFCAB          TRG_INC_UDP_TGFFIN_TRAVA       ENABLED\nTESTE      TGFCAB          TRG_INC_UDP_TGFFIN_TRAVA       DISABLED\n<\/pre><\/div>\n\n\n\n<p><strong>How to Check Triggers TABLE_NAME<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >column OWNER format a10;\ncolumn TABLE_NAME format a15;\ncolumn STATUS format a10;\ncolumn TRIGGER_NAME format a30;\n\nselect OWNER,table_name, trigger_name,status\nFROM dba_triggers\nWHERE table_name = upper ('&amp;table_name');\n\nOWNER      TABLE_NAME      TRIGGER_NAME                   STATUS\n---------- --------------- ------------------------------ ----------\nTREINA     TGFCAB          TRG_INC_UPD_TGFCAB_RASTST      ENABLED\nTREINA     TGFCAB          TRG_INC_UPT_DLT_TGFCAB_INDENIZ ENABLED\nTREINA     TGFCAB          TRG_DLT_TGFCAB_AUDIT_CONF      ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_GRANDES_CARGAS  ENABLED\nTREINA     TGFCAB          TRG_INC_TGFCAB                 ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_TGAMOV          ENABLED\nTREINA     TGFCAB          TRG_DLT_TGFCAB_ESTTERC         ENABLED\nTREINA     TGFCAB          TRG_DLT_TGFCAB_AFTER           ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_FLEX            ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_AFTER           ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_TRANSG          ENABLED\nTREINA     TGFCAB          TRG_INC_UPD_TGFCAB_TGFGXE      ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_SERIE           ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_EST             ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_TGFCPP          ENABLED\nTREINA     TGFCAB          TRG_DLT_TGFCAB_METAS           ENABLED\nTREINA     TGFCAB          TRG_INC_UPT_DLT_TGFCAB_FEC_CTB ENABLED\nTREINA     TGFCAB          TRG_UPD_TGFCAB_TCIBEM          ENABLED<\/pre><\/div>\n\n\n\n<p><strong>How to recreate TRIGGER DDL<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >set pagesize 0\nset long 10000\nSQL &gt; SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRG_INC_UDP_TGFFIN_TRAVA','TESTE') FROM dual;\n\n  CREATE OR REPLACE EDITIONABLE TRIGGER \"TESTE\".\"TRG_INC_UDP_TGFFIN_TRAVA\"\nBEFORE UPDATE ON SANKHYA.TGFCAB FOR EACH ROW\nDECLARE\nVC_COUNT INT;\n\nBEGIN\n\n\nIF (UPDATING('STATUSNOTA'))  THEN\n\nSELECT COUNT(*)\nINTO VC_COUNT\nFROM TGFFIN\nWHERE NUNOTA = :NEW.NUNOTA AND CODTIPTIT = 0;\n\n\nIF VC_COUNT &gt; 0 THEN\n\nRAISE_APPLICATION_ERROR(-20101,Ad_Fc_Formatahtml2('Nao e possivel confirmar a Nota\/Pedido',\n                                                       'Tipo de titulo 0 no financeiro.',\n                                                       'Alterar o tipo de titulo no financeiro'));\nEND IF;\n\nEND IF;\n\nEND;\n\nALTER TRIGGER \"TESTE\".\"TRG_INC_UDP_TGFFIN_TRAVA\" DISABLE<\/pre><\/div>\n\n\n\n<p><strong>How to enable all the trigger on a table<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >ALTER TABLE &lt;table name&gt; enable ALL TRIGGERS;<\/pre><\/div>\n\n\n\n<p><strong>How to list all disabled triggers<\/strong><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >select table_name, trigger_name,status\nFROM user_triggers\nWHERE status='DISABLED';\n\ncolumn OWNER format a10;\ncolumn TABLE_NAME format a15;\ncolumn STATUS format a10;\ncolumn TRIGGER_NAME format a30;\n\nSQL&gt;\nselect OWNER, table_name, trigger_name,status\nFROM dba_triggers\nWHERE status='DISABLED';\n\n\nOWNER      TABLE_NAME      TRIGGER_NAME                   STATUS\n---------- --------------- ------------------------------ ----------\nSANKHYA    TGFITE          AD_TRG_TGFITE_LOTE_NFE_FORNEC  DISABLED\nTESTE      TGFITE          AD_TRG_TGFITE_LOTE_NFE_FORNEC  DISABLED\nTESTE      TGFCAB          TRG_INC_UDP_TGFFIN_TRAVA       DISABLED\nSYS                        SYSLSBY_EDS_DDL_TRIG           DISABLED\nSYS                        LOGMNRGGC_TRIGGER              DISABLED\nWMSYS                      NO_VM_DDL                      DISABLED\nWMSYS                      NO_VM_DROP_A                   DISABLED\nMDSYS                      SDO_TOPO_DROP_FTBL             DISABLED\nLBACSYS                    LBAC$AFTER_DROP                DISABLED\nLBACSYS                    LBAC$BEFORE_ALTER              DISABLED\nLBACSYS                    LBAC$AFTER_CREATE              DISABLED<\/pre><\/div>\n\n\n\n<p><strong>How to List All Triggers in Table<\/strong>s<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:plsql decode:true \" >column OWNER format a10;\ncolumn TABLE_NAME format a15;\ncolumn STATUS format a10;\ncolumn TRIGGER_NAME format a30;\nSQL&gt; select OWNER, table_name, trigger_name,status\n       FROM dba_triggers\n     WHERE status='DISABLED';\n\n\nOWNER      TABLE_NAME      TRIGGER_NAME                   STATUS\n---------- --------------- ------------------------------ ----------\nSANKHYA    TGFITE          AD_TRG_TGFITE_LOTE_NFE_FORNEC  DISABLED\nTESTE      TGFITE          AD_TRG_TGFITE_LOTE_NFE_FORNEC  DISABLED\nTESTE      TGFCAB          TRG_INC_UDP_TGFFIN_TRAVA       DISABLED\nSYS                        SYSLSBY_EDS_DDL_TRIG           DISABLED\nSYS                        LOGMNRGGC_TRIGGER              DISABLED\nWMSYS                      NO_VM_DDL                      DISABLED\nWMSYS                      NO_VM_DROP_A                   DISABLED\nMDSYS                      SDO_TOPO_DROP_FTBL             DISABLED\nLBACSYS                    LBAC$AFTER_DROP                DISABLED\nLBACSYS                    LBAC$BEFORE_ALTER              DISABLED\nLBACSYS                    LBAC$AFTER_CREATE              DISABLED\n<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>How to enable the trigger in Oracle How to disable the trigger in Oracle How to check Trigger status in Oracle How to Check Triggers TABLE_NAME How to recreate TRIGGER DDL How to enable all the trigger on a table How to list all disabled triggers How to List All Triggers in Tables<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"aside","meta":{"footnotes":""},"categories":[1],"tags":[95,399,397,398],"class_list":["post-1199","post","type-post","status-publish","format-aside","hentry","category-sem-categoria","tag-dbms_metadata-get_ddl","tag-table_name","tag-trigger","tag-trigger_name","post_format-post-format-aside"],"_links":{"self":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1199","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=1199"}],"version-history":[{"count":7,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1199\/revisions"}],"predecessor-version":[{"id":1206,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1199\/revisions\/1206"}],"wp:attachment":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=1199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=1199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=1199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}