Re: Drop Trigger IF EXISTS trigger_name

Sorry to hijack your thread Dan, but I have a similiar question and I am
interested in how others handle the situation.

When developing I may create a new table and save the SQL script.
At a later point I realize that I need another column. So in order to rerun
the CREATE TABLE script I have to manually drop the table.
If I had an IF EXISTS I could condition the DROP TABLE statement.

How do others handle this and similiar situations?

Thanks,

Robert Rogerson

On Wed, Mar 9, 2011 at 9:31 AM, Dan <dan27649@xxxxxxxxx> wrote:

Hi Vern,

I considered that as well. Unfortunately, the scripts from multiple
projects are collected and executed by a DBA, and I have no control over
anything outside of my script.

The script is actually used to create a new trigger, and the idea behind
the
"Delete if exists" is, in case this script is run multiple times, for
reasons outside of my control, that it won't bomb if I try to delete a
trigger that doesn't exist. Or bomb when creating a trigger that already
exists. My request is in the realm of "would be nice if we could". We
will
deal with the problem if it arises. My guess is that they activate
journaling before running the scripts, and can roll back changes if
necessary.

Thanks!
- Dan

On Tue, Mar 8, 2011 at 6:23 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Dan

You can set the severity higher on RUNSQLSTM - I assume that is the
command you are using? Other interfaces probably have a similar setting
somewhere.

Prompt RUNSQLSTM and press F10 or F9 to see more parameters, including
severity.

HTH
Vern

On 3/8/2011 3:11 PM, Dan wrote:

Drop Trigger on a trigger that no longer exists causes an SQL0204

message

with severity 30 (<trigger_name> in *LIBL not found) and causes the

script

to abort.

MySQL allows "IF EXISTS" to be used in a Drop Trigger statement. It

does

not appear that we can use this in DB2. Presuming that to be the case,

is

there an easy way to do this without resorting to CL (MONMSG) or an SQL
stored procedure?

TIA,
- Dan

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.