DB2 Trigger – How to Drop Trigger If Exists in DB2

db2db2-luwtrigger

In MySQL, if I have schema myschema and a trigger named mytriggername I can drop a trigger like follows

DROP TRIGGER IF EXISTS myschema.mytriggername

Anyway, I can't find on DB2 docs for the DROP statement any counterpart for the DROP TRIGGER IF EXISTS statement?
Any suggestion?

Best Answer

Another way of doing this is a FOR loop only executing DROP statements for objects that exist. It's frankly overkill for a single object but it's useful when dealing with schema loads of things. For example:

BEGIN ATOMIC
    FOR D AS
        SELECT 'DROP TRIGGER ' || TRIGNAME AS DDL
        FROM SYSCAT.TRIGGERS
        WHERE TRIGSCHEMA = 'SOMESCHEMA'
        AND TRIGNAME = 'SOMETRIGGER'
    DO
        EXECUTE IMMEDIATE D.DDL;
    END FOR;
END@

I used similar tricks in a DROP_SCHEMA routine ages ago (source link at the bottom of the page) although it's redundant now.

The above trick can also be used for conditional upgrades of "only those objects which exist", and/or "only those objects which don't exist".

For actual schema creation I generally preferred keeping a "clean" creation script (no conditional stuff) and a separate "drop" script to clean things up. If that sounds like making redundant work for yourself, there are certain tricks one can employ to generate the drop script from the creation script (use of tac to reverse the install script, and a little regex magic to convert CREATE statements into DROP; it does mean being vaguely disciplined in how you format your SQL though unless you want the regexes to get really messy).

Related Question