This discussion is archived
12 Replies Latest reply: Mar 7, 2012 2:25 PM by 905340 RSS

Follow clause in Oracle 11g Triggers

905340 Newbie
Currently Being Moderated
Hello,
Do we have a way to give Follow clause in a db row level trigger in 11g so that this trigger does the job after all the row level triggers have been fired.
The reason being , i would like to create a dynamic trigger on some tables and i would not know the names of the existing triggers and the order of execution
is not defined in them as well.

Thanks.
  • 1. Re: Follow clause in Oracle 11g Triggers
    sb92075 Guru
    Currently Being Moderated
    902337 wrote:
    Hello,
    Do we have a way to give Follow clause in a db row level trigger in 11g so that this trigger does the job after all the row level triggers have been fired.
    The reason being , i would like to create a dynamic trigger on some tables and i would not know the names of the existing triggers and the order of execution
    is not defined in them as well.

    Thanks.
    Above sounds like kludge, built upon a hack, layered upon 100% flawed "design"!

    Besides it will scale as well as my goat can fly.
  • 2. Re: Follow clause in Oracle 11g Triggers
    rp0428 Guru
    Currently Being Moderated
    >
    Do we have a way to give Follow clause in a db row level trigger in 11g so that this trigger does the job after all the row level triggers have been fired.
    >
    If you mean a 'FIRE_THIS_ONE_LAST' clause no. To ensure that a trigger for a giving timing (e.g. AFTER ROW) is fired after all other similar triggers you need to specifically name ALL of the other triggers.

    See CREATE TRIGGER in the PL/SQL Language Reference - http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm#i2235611
    >
    If FOLLOWS is specified only for some triggers but not all triggers, then the order of execution of triggers is guaranteed only for those that are related using the FOLLOWS clause.
    . . .
    FOLLOWS
    This clause lets you specify the relative firing order of triggers of the same type. Use FOLLOWS to indicate that the trigger being created should fire after the specified triggers.

    The specified triggers must already exist, they must be defined on the same table as the trigger being created, and they must have been successfully compiled. They need not be enabled.
    >
    As for
    >
    i would not know the names of the existing triggers
    >
    You can get the names of the existing triggers from the data dictionary.
    select trigger_name, trigger_type, triggering_event, table_owner, table_name
    from user_triggers
    
    EMP_TRG4     AFTER EACH ROW     INSERT OR UPDATE OR DELETE     SCOTT     EMP3
    EMP_TRG     BEFORE EACH ROW     UPDATE     SCOTT     EMP
    EMP_TRG3     BEFORE EACH ROW     UPDATE     SCOTT     EMP3
  • 3. Re: Follow clause in Oracle 11g Triggers
    rp0428 Guru
    Currently Being Moderated
    >
    Besides it will scale as well as my goat can fly.
    >
    How's that 'goat' training coming anyway? You haven't given us an update lately. If it doesn't work out maybe you can hitch it up to your vehicle and help solve some of those "my car won't go" problems we sometimes hear about.
  • 4. Re: Follow clause in Oracle 11g Triggers
    sb92075 Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    Besides it will scale as well as my goat can fly.
    >
    How's that 'goat' training coming anyway? You haven't given us an update lately. If it doesn't work out maybe you can hitch it up to your vehicle and help solve some of those "my car won't go" problems we sometimes hear about.
    I am struggling to decide which is smarter; my goat or my Pet Rock.
  • 5. Re: Follow clause in Oracle 11g Triggers
    damorgan Oracle ACE Director
    Currently Being Moderated
    You have received a substantial amount of incorrect advice in this thread.

    The FOLLOWS clause is intended, very specifically, to allow you to order trigger firing order.

    That said ... you have provided not nearly enough information from which to conclude you do, or do not, have a good idea.

    1. What version?
    2. Very carefully define what you mean by "dynamic trigger" ... and if you mean DDL on-the-fly it is a bad idea by definition
    3. Why is firing order important to what you are doing
    4. Is edition based redefinition involved?
  • 6. Re: Follow clause in Oracle 11g Triggers
    rp0428 Guru
    Currently Being Moderated
    >
    You have received a substantial amount of incorrect advice in this thread.
    >
    Care to elaborate? If I have given any incorrect advice please point out what it was so I can avoid doing it in the future. What I suggested came entirely from the Oracle 11g documentation.
  • 7. Re: Follow clause in Oracle 11g Triggers
    905340 Newbie
    Currently Being Moderated
    Thanks all for your comments.
    not sure why i did not receive notifications when replied.
    Damorgan sir,
    Answering your questions,
    1) version as mentioned in subject Oracle 11g - Enterprise - 11.2.0.2.0
    2) Sorry for confusion on dynamic trigger, the trigger is going to be created dynamically for some tables through a procedure based on user_tables query to add something to do similar to CDC. This will be just a one time procedure to create triggers for some tables (something like synchronous CDC).
    3) I thought firing order might be important so that any other for each trigger in a table does the job before this executes.
    4) I am not sure what is meant by edition based redefinition.

    Thanks.
  • 8. Re: Follow clause in Oracle 11g Triggers
    905340 Newbie
    Currently Being Moderated
    sb92075 ,
    Thanks for the comments, sorry, i am not as knowledgeable as you are, so, dont know what you you mean..
  • 9. Re: Follow clause in Oracle 11g Triggers
    damorgan Oracle ACE Director
    Currently Being Moderated
    Several people gave advice when the OP clearly did not provide sufficient information, or clarity, to have any idea what they were doing, why they were doing it, etc.

    Here's an example of what I read:
    Question: My car won't start tell me what to do?
    
    Answer: Fill the tank with Petrol.
    The problem, of course, is that no one responding bothered to ask a lot of critically important questions such as the model and make ... perhaps it is an electric car.

    Note that my response was not to provide advice ... but rather to ask the OP to clear up a few of the obvious ambiguities.

    These links to Tom Kyte's might help you understand why I responded as I did.
    http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html
    http://tkyte.blogspot.com/2005/06/how-to-give-answer.html
  • 10. Re: Follow clause in Oracle 11g Triggers
    905340 Newbie
    Currently Being Moderated
    rp0428 ,
    Thanks for your comments.
    Yes, i was looking for like FIRE_THIS_ONE_LAST. but i do understand there s no such thing from the documentation, so, wanted to know mainly whether there s a different way to approach this.
    yes, i also do understand i can get the names from user_triggers but since the order has not been defined currently, would not be able to decide on the follow clause as you know.
  • 11. Re: Follow clause in Oracle 11g Triggers
    damorgan Oracle ACE Director
    Currently Being Moderated
    1. Excellent.

    2. If the point of the proc is to create permanent triggers this is a great way to do it. Far better to build a tool you can reuse than to waste time hand-crafting each trigger by hand. To do what you want you can easily use native dynamic SQL and determine column data types and sizes by looking them up in all_tab_cols or other similar data dictionary views.

    3. If properly written triggers, and most triggers shouldn't exist, no need to worry about their firing order.

    4. Then most likely no need to worry about trigger firing order but do read the docs on Edition Based Redefinition as this is, to use Tom Kyte's words, the "killer feature" in database 11g.
    Here's a link to Bryn Llewellyn's White Paper on the subject: http://www.morganslibrary.org/files/edition_based_redefinition.pdf
    and you will find many of my presentations here:
    http://www.morganslibrary.org/presentations.html
  • 12. Re: Follow clause in Oracle 11g Triggers
    905340 Newbie
    Currently Being Moderated
    Thanks sir for your inputs...
    Indeed, edition concept sounds very interesting, not sure how i missed this one.
    Thanks again..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points