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;

Update

On triggers it is usually not an issue, but you might be modifing other object types too and this script is using VARCHAR2(32k). So you should check, if your object code actually fits into 32k:

WITH t AS (
    SELECT
        t.name,
        t.type,
        COUNT(t.text)       AS object_lines,
        SUM(LENGTH(t.text)) AS object_size
    FROM user_source t
    GROUP BY ALL
)
SELECT
    t.type          AS object_type,
    t.name          AS object_name,
    t.object_lines,
    t.object_size,
    --
    b.object_lines  AS body_lines,
    b.object_size   AS body_size
FROM t
LEFT JOIN t b
    ON b.name   = t.name
    AND b.type  = t.type || ' BODY'
WHERE t.type    NOT LIKE '% BODY'
ORDER BY
    1, 2;