0

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?

  • Just drop and ignore the error if it does not exist? – mustaccio Sep 1 '17 at 14:20
  • @mustaccio because there is no better way or...? – JeanValjean Sep 1 '17 at 14:30
  • "Better" in what sense? What could be better than DROP? Only 4 bytes. – mustaccio Sep 1 '17 at 14:37
2

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).

1

You can create a procedure that ignores the error that's generated if you drop a non-existing trigger:

CREATE PROCEDURE drop_trigger (trigschema   varchar(128), 
                               trigname     varchar(128))
LANGUAGE SQL
BEGIN
    DECLARE tmpstmt VARCHAR(300);
    SET tmpstmt = 'drop trigger ' || trigschema || '.' || trigname;

    A: BEGIN
        -- Do nothing if drop trigger fails
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
            BEGIN
            END;

        EXECUTE IMMEDIATE tmpstmt;
    END;
END @

You can call the procedure like:

CALL drop_trigger('MYSCHEMA','MYTRIGGER');

The long term solution is to use a version for the database(s), and ignore all versions that are already in there. Every commit creates a new version, on error rollback so you never end up between versions. Needless to say, you need to prevent all ddl operations outside of this process.

  • Cool. No way to avoid the sproc? – JeanValjean Sep 1 '17 at 15:48
  • Well, you could use an anonymous block and do the same, but I prefer to encapsulate the functionality in a procedure so that I can reuse it. – Lennart Sep 1 '17 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.