Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    9

    Unanswered: Alter an trigger (source code unknown)

    Hello,
    I've got a programm which creates a trigger. I must change the trigger, but how to do this without the current sqlcode? Is it possible to query the source code of an existing trigger? The DBA studio can show the source code of a trigger in an editor window. How can I realize the changes with a SQL Script?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Attached is a script that will generate a spool file containing the
    sql for the trigger...

    It will prompt you for the table_name and the owner ...

    At the bottom of this script is the spool statement ... change the
    directory structure to match your environment ...

    HTH
    Gregg
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    9

    Smile

    Works fine. Thank you very much, Gregg!

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad it worked out for you ... let me know if you need other things
    like that .... I have accumulated many scripts over the years ...

    Gregg

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    9

    Exclamation Bug?

    Hello Gregg,

    is it possible that there is a bug in line 127 of your script?
    Lv_String := 'ON ' ||
    LOWER(Lv_Trig_Owner) ||
    '.' ||
    LOWER(Lv_Trig_Table_Name);

    should be
    Lv_String := 'ON ' ||
    LOWER(Lv_Trig_Table_Owner)
    '.' ||
    LOWER(Lv_Trig_Table_Name);
    shouldn't it be??

    These lines create the "ON"-Part of a string like this one:
    CREATE OR REPLACE TRIGGER triggerOwner.triggerName INSTEAD OF INSERT OR UPDATE OR DELETE
    ON tableOwner.tableName

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You can query a DBA_SOURCE.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Looks like a problem ... I always run the script as the schema owner ...

    Sorry
    Gregg

Posting Permissions

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