DISBALE/ENABLE A TRIGGER IN THE PROCEDURE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: DISBALE/ENABLE A TRIGGER IN THE PROCEDURE

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    I have the procedure and what it does is to insert data into the table.

    the question is how can I disable a trigger in the procedure. Could someone please give me the syntax of how to do this

    thanks much


  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    You can issue an execute immediate similar to the following:

    sql_code := 'ALTER TRIGGER user.xyz DISABLE;';
    execute immediate sql_code;

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'll have to put on my chrisrlong hat...

    A trigger was implemented on that table for a specific reason. Either the trigger doesn't do what it should, or you are trying to circumvent the design. Either way, a trigger should not be disabled during a procedure. What if other users were doing an operation on the table that needed the trigger? What if your procedure failed and never re-enabled the trigger? Any way you look at it, it's a bad idea.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2002
    Posts
    474
    Jeff,

    thanks for yoru advise but in this particular situation, the users want to disable the trigger.


    Jovery,

    Thanks for the advise but the code is not quite working yet.

    when I declare SQL_CODE, can I declare it as


    sql_code varchar2(100); ??????


    B/c the code won't compile if you don't declare

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Sorry I missed that line out, yes you should declare it.

    Your suggestion is fine

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Jan 2002
    Posts
    474
    for some reason I got


    Error-911 ORA-00911:L invalid character

    Please advise

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jovery
    You can issue an execute immediate similar to the following:

    sql_code := 'ALTER TRIGGER user.xyz DISABLE;';
    execute immediate sql_code;

    Regards
    sql_code := 'ALTER TRIGGER user.xyz DISABLE';
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ashley75
    Jeff,

    thanks for yoru advise but in this particular situation, the users want to disable the trigger.

    Thank God I'm not responsible for THAT database!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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


Click Here to Expand Forum to Full Width