SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditional (IF EXIST) DROP TRIGGER

    can anyone tell me if you can do:

    DROP TRIGGER IF EXISTS `companymanagementmysql`.`tr_tblbasketitem_bi`

    It's very annoying having to delete a trigger manually then run a script to re-create it.. Is there a way of testing it the trigger exists before re-creating it???

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in case there are differences between one database system and the next, it's always a good idea to mention which one you're using

    that said, why not just place an unqualified DROP statement in the script, and ignore or suppress the error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm very sorry, i'm upto my eyeballs in it and forget ppl use other stuff.. LOL.. I'm on MySQL 5.0.2...

    I'm using the MySQL Query Browser to run scripts to create Procedures and Triggers... If i use an unconditional DROP statement, it stops running the code if there is no Trigger to drop...

    Would it be easy to query the Trigger Table to see if it's there, or is that not very easy/viable?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sylmand
    If i use an unconditional DROP statement, it stops running the code if there is no Trigger to drop...
    i understand what you're saying, but isn't there some sort of flag you can set that suppresses errors and allows the script to keep running?

    you could alternatively do a query on the INFORMATION_SCHEMA TRIGGERS table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Posts
    30
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A - I've checked the OPTIONS within the Query BRowser and cannot find anything, unless anyone can say other wise...
    B - How would i query the Triggers table... (exampleswuld be helpfull, as i'm fairly new to this)..

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sylmand
    B - How would i query the Triggers table...
    something like:
    Code:
    select count(*) as yes_no
      from information_schema.triggers
     where trigger_name = 'name of trigger you want to drop'
    but i think if you want this as part of a script you'll have to use a stored procedure, in which you can use an IF statement to decide whether to execute the DROP command
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •