Update all triggers via script

S

Sometimes, you have to modify all triggers, if you were for example using SYSTIMESTAMP and now SYSDATE is enough. To modify all triggers by hand might be a lot of work (it is not just about modifing, you also have to compile all of them in the database).

So let's write a script for that (replace LOCALTIMESTAMP with SYSDATE in all triggers), but you can easily modify it to whatever you need to change:

DECLARE
    v_query             VARCHAR2(32767);
    v_modified          BOOLEAN;
BEGIN
    FOR c IN (
        SELECT
            t.name,
            ROWNUM AS r#,
            d.referenced_name
        FROM user_source t
        JOIN user_dependencies d
            ON d.name               = t.name
            AND d.name              LIKE '%'
            AND d.type              = t.type
            AND d.type              = 'TRIGGER'
            AND d.referenced_type   = 'TABLE'
        WHERE t.line = 1
        ORDER BY 1
    ) LOOP
        v_query     := '';
        v_modified  := FALSE;
        --
        FOR d IN (
            SELECT
                t.text
            FROM user_source t
            WHERE 1 = 1
                AND t.type = 'TRIGGER'
                AND t.name = c.name
            ORDER BY
                t.line
        ) LOOP
            IF UPPER(d.text) LIKE '%LOCALTIMESTAMP%' THEN
                d.text := REGEXP_REPLACE(d.text, 'LOCALTIMESTAMP', 'SYSDATE', 1, 1, 'i');
                --
                v_modified := TRUE;
            END IF;
            --
            v_query := v_query || d.text;
        END LOOP;
        --
        IF v_modified THEN
            DBMS_OUTPUT.PUT_LINE('MODIFIED: ' || c.referenced_name || ' ' || c.name);
            EXECUTE IMMEDIATE
                'CREATE OR REPLACE ' || v_query;
        END IF;
    END LOOP;
END;
/

And this query you can use as an overview on your dashboard:

SELECT
    t.table_name,
    --
    g.trigger_name,
    g.trigger_type,
    g.triggering_event,
    g.before_statement,
    g.before_row,
    g.after_row,
    g.after_statement,
    g.status,
    --
    o.last_ddl_time
    --
FROM user_tables t
JOIN user_triggers g
    ON g.table_name     = t.table_name
JOIN user_objects o
    ON o.object_name    = g.trigger_name
WHERE t.table_name      LIKE '%' ESCAPE '\'
ORDER BY 1, 2;