Re: Drop Trigger IF EXISTS trigger_name

[Robert Rogerson <rogersonra@xxxxxxxxx>]

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.